SQL UPDATE Statement

The UPDATE statement is used to modify existing records in a table. You can update specific records using the WHERE clause, or update all records in a table.

In the statement below, we want to update the address of the customer with CustomerID 1.

Example​

Update the address for the customer with CustomerID equal to 1:

UPDATE Customers
SET Address = 'New Address, 123'
WHERE CustomerID = 1;

In the example above, the SET clause specifies the column(s) to update, and the WHERE clause specifies which record(s) to modify.

Video Explanation​

Watch

Syntax​

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

If you omit the WHERE clause, all records in the table will be updated.

Demo Database​

Below is a selection from the Customers table used in the examples:

Updating Multiple Columns​

Example​

Update both the Address and City for the customer with CustomerID equal to 2:

UPDATE Customers
SET Address = 'New Street 456', City = 'Berlin'
WHERE CustomerID = 2;

Updating All Records​

Example​

Increase all customer postal codes by 100:

UPDATE Customers
SET PostalCode = PostalCode + 100;

Note: Be careful when using UPDATE without a WHERE clause, as it will modify all records in the table.

Using Subquery in UPDATE​

Example​

Update customers’ City based on another table called Orders:

UPDATE Customers
SET City = (SELECT ShipCity FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)
WHERE Country = 'USA';

In this example, a subquery is used to select the ShipCity from the Orders table for updating City in the Customers table.

Conditional Update with CASE​

Example​

Update the City based on customer conditions using the CASE statement:

UPDATE Customers
SET City = CASE
              WHEN Country = 'Mexico' THEN 'Mexico City'
              WHEN Country = 'Germany' THEN 'Munich'
              ELSE City
           END;

The CASE statement is used here to conditionally update the City field based on the Country field.