The UPDATE changes rows that already exist, and the only thing that stands between it and a catastrophe is the WHERE clause. One missing condition rewrites the whole table. The rest of the skill is knowing what to put in the SET clause, whether that is a literal, an expression, or a value computed from another table, and how to keep the blast radius small.
The previous lesson in this module covered "How Does INSERT Add Rows Correctly?", and the next lesson in this module is about "What Is the Difference Between DELETE and TRUNCATE?".
How does the UPDATE change the rows?
The statement has two halves. The SET says what changes, and the WHERE says which rows change.
A seat was swapped, so one row moves.
UPDATE tickets SET seat_code = 'B-08' WHERE ticket_id = 3;
SELECT ticket_id, seat_code FROM tickets WHERE ticket_id = 3;ticket_id seat_code
--------- ---------
3 B-08The column list in the SET can hold several assignments separated by commas, and the right-hand side of each one can be an expression over the current row. Here the prices for one event all move to a flat rate.
UPDATE tickets SET price_paid = 20.00 WHERE event_id = 4;
SELECT ticket_id, event_id, price_paid FROM tickets WHERE event_id = 4 ORDER BY ticket_id;ticket_id event_id price_paid
--------- -------- ---------
8 4 20.0
9 4 20.0
10 4 20.0Three rows, one event, one price.
Notice how many of the statements have touched this table in lesson 32. The inserts, the deletes and the updates all land in the same place, and the SELECT that comes afterwards is how you check what happened.
Why is the WHERE clause the safety system?
It is the safety system because an UPDATE without a WHERE clause applies to every row in the table.
Here is what that looks like. A ten per cent discount is applied to the sold tickets of one event, and the refunded ticket is deliberately left alone.
UPDATE tickets SET price_paid = ROUND(price_paid * 0.9, 2) WHERE event_id = 2 AND status = 'sold';
SELECT ticket_id, price_paid, status FROM tickets WHERE event_id = 2 ORDER BY ticket_id;ticket_id price_paid status
--------- ---------- -------
4 19.8 sold
5 19.8 sold
15 22.0 refundedThere are two conditions, and the second one is the interesting one. Ticket 15 was already refunded, so its price is a historical fact and it must not be rewritten.
A condition that names the event alone would have discounted a refunded ticket, and that is a small and entirely believable bug.
How do you update a row from another table?
There are two shapes that cover it.
The portable one puts a subquery in the SET clause, and the WHERE clause names the rows to change by using another subquery.
UPDATE tickets
SET status = 'refunded'
WHERE holder_id IN (SELECT holder_id FROM ticket_holders WHERE city = 'Glasgow');
SELECT ticket_id, holder_id, status FROM tickets
WHERE holder_id IN (SELECT holder_id FROM ticket_holders WHERE city = 'Glasgow')
ORDER BY ticket_id;ticket_id holder_id status
--------- --------- -------
4 4 refunded
12 4 refundedLukas Meyer is the only Glasgow-based holder, and both of his tickets now read refunded.
The newer syntax joins the table that is being updated to the table that supplies the values. SQLite supports it from version 3.33, PostgreSQL has it, and the closest equivalent in MySQL is the multi-table UPDATE. Here every ticket for one event is repriced at 80 per cent of the event's list price.
UPDATE tickets
SET price_paid = ROUND(e.ticket_price * 0.8, 2)
FROM events e
WHERE e.event_id = tickets.event_id AND e.event_id = 6;
SELECT ticket_id, event_id, price_paid FROM tickets WHERE event_id = 6 ORDER BY ticket_id;ticket_id event_id price_paid
--------- -------- ---------
13 6 13.2
14 6 13.2
16 6 13.2That is thirteen pounds and twenty pence, down from the sixteen-fifty ticket.
The join tells you which prices moved and why, and that is worth a lot when you come back to this query in six months and cannot remember what the 0.8 stood for.
What does a missing WHERE clause actually cost?
Here is the accident, run inside a transaction so that it can be undone. An UPDATE without a WHERE clause, and every ticket in the table becomes free.
BEGIN;
UPDATE tickets SET price_paid = 0;
SELECT COUNT(*) AS zero_priced_rows FROM tickets WHERE price_paid = 0;zero_priced_rows
16ROLLBACK;
SELECT COUNT(*) AS zero_priced_rows_after_rollback FROM tickets WHERE price_paid = 0;zero_priced_rows_after_rollback
-------------------------------
0Every single row, in one statement, undone by the rollback.
This is why the habit matters. You open a transaction before a destructive UPDATE, you write the WHERE clause, you check the row count, and only then do you commit. If the count is not what you expected, the rollback is already in place.
Lesson 36 makes that habit explicit, and lesson 37 makes it finer-grained.
Quick reference
An UPDATE that matches no rows is a quieter failure. You are setting every refunded ticket back to sold, but for a seat code that does not exist, so it changes nothing at all.
UPDATE tickets SET status = 'sold' WHERE seat_code = 'Z-99';
SELECT COUNT(*) AS refunded_left FROM tickets WHERE status = 'refunded';refunded_left
-------------
3There is no error and there is no warning, and the three refunded tickets are still refunded.
A silent no-op is usually a typo in the WHERE clause, and the only reliable detection is to check how many rows the statement was supposed to touch.
- The SET says what changes and the WHERE says which rows change. You never ship an UPDATE without a WHERE clause.
- The right-hand side of a SET assignment can be an expression over the current row.
- You add conditions for the business meaning and not just for the identifier, so that the history stays intact.
- You update from another table with a subquery in the SET, or with
UPDATE ... FROMwhere the engine supports it. - You check the affected row count, because an UPDATE that matches nothing reports no error at all.
Update drill: you raise the price of every ticket at the Comedy Cellar to the current list price of the event, using a single UPDATE that is joined to the events table. Then you write a second statement that refunds every ticket that belongs to the holders who signed up in the year 2024, and you say which of the two would be safer inside a transaction.