The UPDATE statement is used to modify the existing records in a table.

The syntax is as follows.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let’s look at an example. Below we are adding values to the ProductSales column in the OrderDetails table. We are multiplying the price by the quantity and then subtracting the Discount.

UPDATE [OrderDetails]
  SET ProductSales = (UnitPrice * Quantity * (1 - Discount));

Have a look at another post called SQL Add Column that has a script that creates the table, adds some data to the table, creates a new column, and then adds some data to that new column.

We can see how to update a column based on other columns in the same table. What if we wanted to update a column based on values in another table?

If you are working in Python, with a DataFrame (analogous to a table), how would you do this conditional update in Python? Here is a post called Replace Values in Pandas.

Python

Are you working with a dataset in Python and are you wondering what the similar code would be? We have a post here called Data Imputation of Negative Numbers. Suppose, as in that example, you need to change all negative numbers to zero, because a distance or time or height or weight does not make sense as a negative number. Here’s the code

# Impute duration values less than 0 with 0 in the dataset df.
df.loc[df['duration'] < 0, 'duration'] = 0
df['duration'].min()

This is not unlike the concept of a search and replace.