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
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 category2.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_priceof each product whosecategory_idis2. - 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.