ANSI Standard Syntax for UPDATE

Change existing rows with UPDATE ... SET, and always limit it with a WHERE clause you've checked with a SELECT first.

Apprentice · SQL · MySQL · Updated

By

The ANSI standard syntax for updating data in a SQL table involves using the UPDATE statement. This statement allows you to modify existing data in one or more rows of a table. Here is the general syntax and some examples to illustrate its use:

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

table_name: The name of the table you want to update.

SET: The clause used to specify the columns to be updated and their new values.

column1, column2, ...: The columns you want to update.

value1, value2, ...: The new values for the specified columns.

WHERE: The clause used to specify which rows should be updated. If omitted, all rows in the table will be updated.

Basic Examples of Using the UPDATE Statement

Updating a Single Column

UPDATE customers
SET contact_name = 'Alfred Blauer'
WHERE customer_id = 'ALFKI';

This query updates the contact_name column to 'Alfred Blauer' for the customer with customer_id 'ALFKI'.

Updating Multiple Columns

UPDATE products
SET unit_price = 19.99
    , units_in_stock = 25
WHERE product_id = 5;

This query updates both the unit_price and units_in_stock columns for the product with product_id 5.

Updating Multiple Rows

UPDATE customers
SET city = 'San Francisco'
WHERE city = 'Los Angeles';

This query updates the city column to 'San Francisco' for all customers currently located in 'Los Angeles'.

Example Without a WHERE Clause (Updating All Rows)

You should always use a WHERE clause when using an update statement. Otherwise, you will update all records in the table. In fact, MySQL Workbench prevents you from executing an update without a WHERE clause by default. You have to change your settings to allow the UPDATE statement to run without WHERE.

UPDATE orders
SET shipped_date = '2024-04-01';

This query sets the shipped_date column to '2024-04-01' for ALL rows in the orders table. This is almost never a scenario that you want to do in a production environment.

Example with a Complex Condition

You can use complex conditions in the WHERE clause to update specific rows based on multiple criteria.

UPDATE products
SET product_name = CONCAT(product_name, ' (premium)')
    , unit_price = unit_price * 1.20
WHERE category_id = 1
    AND unit_price > 50;

This query adds (premium) to the end of the product_name, and raises the unit_price by 20%, for every product in the Beverages category (category 1) that costs more than $50. In MySQL, CONCAT joins strings together; + only adds numbers.

Summary

When performing an UPDATE statement, you should always use a WHERE clause. In fact, before performing the UPDATE, you should run a SELECT statement with the same WHERE clause so that you can verify that the correct rows will be affected.

Example

SELECT *
FROM products
WHERE product_id = 15;

You can write and execute the UPDATE statement after verifying that the correct product was targeted with the WHERE clause.

UPDATE products
SET unit_price = unit_price * 1.10
WHERE product_id = 15;

Do it: raise the Condiments prices by $1.50

The company that makes Northwind's sauces and spreads has raised its prices, so every product in the Condiments category now costs $1.50 more. Products in other categories keep their prices.

The products table (the Tables tab shows the SQL that makes it) has four columns:

  • product_id: the product's number.
  • product_name: the product's name.
  • category_id: the product's category. Condiments is category 2.
  • unit_price: the price of one.

It has these rows. The three Condiments are the ones with category_id 2:

product_id product_name category_id unit_price After your UPDATE
1 Chai 1 18.00 18.00 (not Condiments: no change)
2 Chang 1 19.00 19.00 (no change)
3 Aniseed Syrup 2 10.00 10.00 + 1.50 = 11.50
4 Chef Anton's Cajun Seasoning 2 22.00 22.00 + 1.50 = 23.50
6 Grandma's Boysenberry Spread 2 25.00 25.00 + 1.50 = 26.50
11 Queso Cabrales 4 21.00 21.00 (no change)

After your SQL runs, the check reads product_id, product_name and unit_price of every product, sorted by product_id, and it should give exactly the prices in the last column.

Your job: write one UPDATE on products.

  • Add 1.50 to the unit_price of each product whose category_id is 2.
  • Work the new price out from the row's current price, so the same statement works for every Condiments product.
  • Every product in another category keeps its price.

Practiced live on the lesson page: write the SQL right there and check it.

Cheat sheets