MySQL – UPDATE

UPDATE changes rows that already exist. It is the statement most likely to cause an incident, because the difference between correct and catastrophic is one clause you can forget to type.

The basic form

UPDATE product_size SET price = price * 1.10 WHERE product_id = 2;

SELECT p.name, ps.size, ps.price FROM product_size ps JOIN product p ON p.id=ps.product_id WHERE p.id = 2 ORDER BY ps.price;

+--------------+--------+-------+
| name         | size   | price |
+--------------+--------+-------+
| Cheese Pizza | SMALL  | 10.99 |
| Cheese Pizza | MEDIUM | 14.29 |
| Cheese Pizza | LARGE  | 17.04 |
+--------------+--------+-------+

SET takes several assignments separated by commas, and the right-hand side can reference the current value or other columns of the same row. Small prices are unchanged by rounding here because price is DECIMAL(10,2) — the arithmetic is exact, which is the point of using DECIMAL for money.

The habit that saves you

Run it as a SELECT first. Same FROM, same WHERE:

-- 1. look
SELECT id, price FROM product_size WHERE product_id = 2;

-- 2. then change exactly those rows
UPDATE product_size SET price = price * 1.10 WHERE product_id = 2;

It costs seconds and it catches the two mistakes that matter: a WHERE that matches more rows than you expected, and one that matches none because of a typo. In a transaction you get a second safety net — UPDATE, check the row count, then COMMIT or ROLLBACK.

sql_safe_updates

-- ERROR 1175 (HY000): You are using safe update mode and you tried to update a
-- table without a WHERE that uses a KEY column.
SET sql_safe_updates = 1;
UPDATE product SET active = FALSE;

With sql_safe_updates on, MySQL refuses an UPDATE or DELETE whose WHERE does not use a key — which is exactly the shape of the statement that empties a table by accident. MySQL Workbench turns it on by default; the command line does not.

Turn it on in your own session on any server that matters. When you genuinely mean to update every row, add LIMIT (or switch it off deliberately for that one statement) — and having to think about it is the entire benefit.

UPDATE with a JOIN

The standard shape for a backfill: compute a value from another table and write it back.

UPDATE customer_order o
JOIN   (SELECT order_id, SUM(line_total) AS s FROM order_item GROUP BY order_id) t
       ON t.order_id = o.id
SET    o.subtotal = t.s;

SELECT id, subtotal, total FROM customer_order ORDER BY id LIMIT 4;

+----+----------+-------+
| id | subtotal | total |
+----+----------+-------+
|  1 |    22.97 | 28.91 |
|  2 |    16.99 | 18.43 |
|  3 |    25.98 | 32.18 |
|  4 |    25.98 | 32.18 |
+----+----------+-------+

The syntax puts the join before SET, which reads oddly the first time. Note that UPDATE ... JOIN does not accept ORDER BY or LIMIT — which matters for the batching section below.

MySQL also forbids updating a table while selecting from it in a subquery ("You can't specify target table for update in FROM clause"). The workaround is to wrap the subquery in another derived table, which forces MySQL to materialise it first — or, more readably, use the join form above.

Updating from a correlated subquery

UPDATE customer_order o
SET    o.subtotal = (SELECT COALESCE(SUM(i.line_total), 0) FROM order_item i WHERE i.order_id = o.id)
WHERE  o.status = 'COMPLETED';

Equivalent to the join for this case, and it has one important difference: a correlated subquery runs for every row the WHERE selects, including rows with no matching items — which is why the COALESCE is there, since SUM over nothing is NULL rather than 0. The join form simply skips unmatched rows. Choose based on whether "no matching rows" should mean zero or mean leave-it-alone.

Updating in batches

One UPDATE touching a million rows holds locks and builds an enormous undo log for its whole duration, which on a live system means blocked writers and replication lag. Work through it instead:

-- repeat until it reports 0 rows changed
UPDATE customer_order
SET    status = 'ARCHIVED'
WHERE  status = 'COMPLETED' AND created_at < '2024-01-01'
ORDER  BY id
LIMIT  1000;

ORDER BY with LIMIT makes each batch deterministic — without it you get "any 1,000 rows". The WHERE has to exclude rows already done, or the loop never terminates; here the status change does that naturally. See running queries in production.

Two smaller things

Rows matched versus rows changed. MySQL reports both, and they differ when a value is set to what it already was. Setting a column to its current value counts as matched but not changed — useful when checking whether an update actually did anything.

Assignments are evaluated left to right within one SET, so SET a = b, b = a does not swap them: by the time b is assigned, a already holds the old b. Swap via a third column, or use the fact that MySQL evaluates the whole right-hand side of each assignment before assigning it.

What to remember

• Run the SELECT first. Every time.
• Turn on sql_safe_updates anywhere that matters.
• UPDATE ... JOIN for backfills; it takes no LIMIT.
• Batch large updates with ORDER BY … LIMIT and a WHERE that shrinks.