The UPDATE Statement

The UPDATE statement is used to change one or some of the values in a data row. As with DELETE, you include a WHERE clause to indicate that this row or rows are to be updated.

Updating

Without a WHERE clause, UPDATE performs the same update on every row in the table. Unless this is the result you want, always include a WHERE clause in an UPDATE statement.

You specify the values to be changed in a list after the SET keyword, separated with commas. The following example updates the name and price of an existing product:

 mysql> UPDATE products     -> SET name = 'Large product (new and improved)',     ->     price = 23.99     -> WHERE product_code = 'MAXI'; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 

The response from MySQL confirms that the update was successful and reports that one row was changed. The numbers against Rows matched and Changed in the output might be different: The former tells you how many data rows were matched by the WHERE clause, whereas the latter indicates how many were altered from their previous value.

If you were to issue the previous statement again, you would see this output instead because the column values are already the same as the values in the UPDATE statement:

 Query OK, 0 rows affected (0.01 sec) Rows matched: 1  Changed: 0  Warnings: 0 

Alternative INSERT Syntax

MySQL enables you to use a similar syntax to UPDATE for INSERT statements, so you can say INSERT INTO table SET col1 = 'val1', SET col2 = .... This is particularly useful when you are generating SQL statements from a client application for either an INSERT or an UPDATE, depending on whether the data row exists yet.

Using this method, you need to generate the keyword at the beginning of the statement and specify a WHERE clause only if it is an UPDATE to change its behavior.

Updating with a Derived Column

You can use a calculation, function, or even subquery as the value in an UPDATE statement. The following is a simple example that adds 10% to every price in the products table:

 mysql> UPDATE products     -> SET price = price * 1.10; Query OK, 3 rows affected, 3 warnings (0.00 sec) Rows matched: 3  Changed: 3  Warnings: 3 

This statement does not require a WHERE clause because you want the calculation to apply to every row in the table. MySQL calculates the value of price * 1.10 for each row in the table and uses that value to update the corresponding row.

The following example uses a subquery to set the prices for each product based on its popularity. Each item's price will be set to $10 plus 10¢ for each time the product has been ordered.

 mysql> UPDATE products p     -> SET price = 10 + (     ->   SELECT SUM(quantity) * 0.10     ->   FROM order_lines o     ->   WHERE o.product_code = p.product_code     -> ); Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 3  Changed: 3  Warnings: 1 

Examining the products table will show that the prices have been updated and that each has a different value.

 mysql> SELECT * FROM products; +--------------+-----------------------------+--------+-------+ | product_code | name                        | weight | price | +--------------+-----------------------------+--------+-------+ | MINI         | Small product               |   1.50 | 13.60 | | MIDI         | Medium product              |   4.50 | 11.00 | | MAXI         | Large product (new formula) |   8.00 | 14.80 | +--------------+-----------------------------+--------+-------+ 3 rows in set (0.00 sec) 

Updating with a Subquery

Just as when you use a subquery in a filter, a subquery used to perform an insert must return only one row and one column. Otherwise, MySQL gives an error.