UPDATE changes rows that already exist, and the only thing standing between it and a catastrophe is the WHERE clause. One missing condition rewrites the entire table. The rest of the skill is knowing what to put in the SET clause — a literal, an expression, or a value computed from another table — and how to keep the blast radius small.
How does UPDATE change rows?
The statement has two halves: SET says what changes, 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-08
The column list in SET can hold several assignments separated by commas, and the right-hand side of each can be an expression over the current row. Prices for one event are all moved 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.0
Three rows, one event, one price. Notice how many statements touched this table in lesson 32 — inserts, deletes and updates all land in the same place, and the SELECT afterwards is how you verify what happened.
Why is the WHERE clause 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 applied to the sold tickets of one event, with the refunded ticket 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 refunded
Two conditions, and the second one is the interesting one: ticket 15 was already refunded, so its price is a historical fact and must not be rewritten. A condition that names the event alone would have discounted a refunded ticket, which is a small, entirely believable bug.
How do you update a row from another table?
Two shapes cover it. The portable one puts a subquery in the SET clause, and the WHERE clause names the rows to change 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 refunded
Lukas Meyer is the only Glasgow-based holder, and both of his tickets now read refunded. The newer syntax joins the table being updated to the table supplying the values — SQLite supports it from version 3.33, PostgreSQL has it, and MySQL's closest equivalent is a 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.2
Thirteen pounds twenty, down from a sixteen-fifty ticket. The join tells you which prices moved and why, which is worth a great deal 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 it can be undone. 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
16
ROLLBACK;
SELECT COUNT(*) AS zero_priced_rows_after_rollback FROM tickets WHERE price_paid = 0;
zero_priced_rows_after_rollback
-------------------------------
0
Every single row, in one statement, undone by the rollback. This is why the habit matters: open a transaction before a destructive UPDATE, write the WHERE clause, check the row count, and only then commit. If the count is not what you expected, the rollback is already there. Lesson 36 makes that habit explicit, and lesson 37 makes it finer-grained.
An UPDATE that matches no rows is a quieter failure. Setting every refunded ticket back to sold, but for a seat code that does not exist, 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
-------------
3
No error, no warning, three refunded tickets 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.
Quick reference
- SET says what changes, WHERE says which rows change — never ship an UPDATE without a WHERE clause.
- The right-hand side of a SET assignment can be an expression over the current row.
- Add conditions for the business meaning, not just the identifier, so history stays intact.
- Update from another table with a subquery in SET, or with UPDATE ... FROM where the engine supports it.
- Check the affected row count; an UPDATE that matches nothing reports no error at all.
Update drill: raise the price of every ticket at the Comedy Cellar to the event's current list price using a single UPDATE joined to the events table — then write a second statement that refunds every ticket belonging to holders who signed up in 2024, and say which of the two would be safer inside a transaction.