UPDATE: changing rows

Changing what is already stored

Prices change, people move house, orders get shipped. UPDATE rewrites the values of existing rows. Its shape is:

UPDATE table
SET    column = new_value [, column = new_value ...]
WHERE  condition;

The WHERE clause selects the rows. Everything after SET says what to put in them.

UPDATE customers SET phone = '+44-20-5555-0002' WHERE id = 2;

SELECT id, first_name, phone FROM customers WHERE id = 2;

+----+------------+------------------+
| id | first_name | phone            |
+----+------------+------------------+
|  2 | Ben        | +44-20-5555-0002 |
+----+------------+------------------+

Ben's phone was NULL; now it holds a number.

Never forget the WHERE

This is the single most important rule of the lesson. If you leave the WHERE out, MySQL does exactly what you asked and changes every row:

UPDATE products SET stock = 0;

SELECT COUNT(*) AS products_with_zero_stock FROM products WHERE stock = 0;

+--------------------------+
| products_with_zero_stock |
+--------------------------+
|                       15 |
+--------------------------+

Every product now has zero stock. There is no undo button unless you used a transaction or have a backup. In this course, each lesson starts from a fresh copy of the database, so that mistake costs nothing here, but on a real system it could cost a business a day of work.

Build a safe routine:

1. Write the condition as a SELECT and look at the rows.
2. Change SELECT ... to UPDATE ... SET ..., keeping the identical WHERE.
3. Check the number of rows affected.
4. For important changes, use a transaction, which a later lesson covers.

MySQL's command-line client even has a --safe-updates option (also named --i-am-a-dummy) that refuses UPDATE and DELETE without a key-based WHERE.

Several columns at once

Separate assignments with commas, not with AND:

UPDATE customers SET city = 'Gurugram', country = 'India' WHERE id = 1;

SELECT id, first_name, city, country FROM customers WHERE id = 1;

+----+------------+----------+---------+
| id | first_name | city     | country |
+----+------------+----------+---------+
|  1 | Asha       | Gurugram | India   |
+----+------------+----------+---------+

Using the current value

The right-hand side can be an expression that uses the existing value. This is how you raise prices or reduce stock:

UPDATE products SET price = price * 1.10 WHERE category_id = 2;

SELECT name, price FROM products WHERE category_id = 2;

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| SQL Basics Handbook         | 32.95 |
| Web Design Patterns         | 37.95 |
| Data Structures Illustrated | 49.50 |
+-----------------------------+-------+

Prices of the three books went up by 10 percent and were rounded to two decimals because the column is DECIMAL(10,2). Doing the arithmetic inside the statement, as in stock = stock - 1, is safer than reading the value into your application, changing it and writing it back, because it cannot lose an update made by someone else in between.

How many rows changed?

After an update, ROW_COUNT() tells you how many rows were actually changed:

UPDATE products SET stock = stock + 10 WHERE id IN (1, 2, 3);
SELECT ROW_COUNT() AS changed_rows;

+--------------+
| changed_rows |
+--------------+
|            3 |
+--------------+

A subtle point: if a row already holds the value you are setting, it is matched but not changed, and it is not counted in ROW_COUNT(). The command-line client prints "Rows matched" and "Changed" for this reason.

UPDATE products SET price = 24.99 WHERE id = 1;
SELECT ROW_COUNT() AS changed_rows;

+--------------+
| changed_rows |
+--------------+
|            0 |
+--------------+

Updating with ORDER BY and LIMIT

MySQL allows ORDER BY and LIMIT on a single-table update, which is handy for "fix just the oldest ones":

UPDATE orders SET status = 'cancelled' WHERE status = 'pending' ORDER BY order_date LIMIT 1;
SELECT id, order_date, status FROM orders WHERE id IN (9, 14, 19);

+----+------------+-----------+
| id | order_date | status    |
+----+------------+-----------+
|  9 | 2024-03-30 | cancelled |
| 14 | 2024-05-15 | pending   |
| 19 | 2024-07-07 | pending   |
+----+------------+-----------+

Updating from another table is also possible with a join. You will see that after you learn joins.

Common mistakes

• Missing WHERE, updating the whole table.
• Using AND between assignments: SET a = 1 AND b = 2 assigns the result of a logical test to a. Use a comma.
• A wrong condition that matches more rows than expected. Test with SELECT first.
• Updating a primary key that other tables reference.
• Doing read-modify-write in application code instead of stock = stock - 1.

Exercise

In the sample shop, mark order 9 as shipped, then give every Home product (category 3) a 5 percent discount. Check the results with SELECT queries.

UPDATE orders SET status = 'shipped' WHERE id = 9;
UPDATE products SET price = price * 0.95 WHERE category_id = 3;
SELECT id, status FROM orders WHERE id = 9;
SELECT name, price FROM products WHERE category_id = 3;

+----+---------+
| id | status  |
+----+---------+
|  9 | shipped |
+----+---------+
+-------------------+-------+
| name              | price |
+-------------------+-------+
| Desk Lamp         | 30.40 |
| Ceramic Mug Set   | 17.81 |
| Standing Desk Mat | 52.25 |
+-------------------+-------+