UPDATE and DELETE: rehearse it as a SELECT

The statement that changes every row

UPDATE orders SET status = 'cancelled';

There is no WHERE clause. Every order in the table is now cancelled. The database reports UPDATE 31940 and waits for the next command. It has done exactly what it was told, and what it was told was a typo: a WHERE clause on the next line that was never sent, or a query pasted without its last line.

Every person who works with databases has a version of this story, and the ones who only have one version learned the habit in this lesson.

Rehearse

Before any UPDATE or DELETE, run the same WHERE clause as a SELECT, and look at what comes back:

SELECT count(*), min(placed_at), max(placed_at)
FROM orders
WHERE status = 'pendng';
-- 3 rows, all from last Tuesday

Three rows, last Tuesday. That is what you expected: a typo in the data from one bad import. Now, and only now, convert the SELECT into the write:

UPDATE orders SET status = 'pending' WHERE status = 'pendng';
-- UPDATE 3

The row count after the write must match the count from the rehearsal. If it says UPDATE 40000, stop, because something between the rehearsal and the write changed, and the transaction lesson next explains how to take it back.

RETURNING shows what was changed, which is the rehearsal’s other half:

DELETE FROM orders WHERE status = 'test' RETURNING id, customer_id, amount;

Read the returned rows. If one of them is a real customer, you know before the transaction is committed.

UPDATE with a join

Correcting one table from another is UPDATE ... FROM:

UPDATE orders o
SET    city = c.city
FROM   customers c
WHERE  c.id = o.customer_id
  AND  o.city IS NULL;

Each order with a missing city takes it from its customer. This has the join hazard the whole course has been about. If the FROM matches more than one row per order, Postgres updates the order with one of them and does not say which, and MySQL and SQL Server have their own versions of the same silence. The rehearsal for an UPDATE FROM is therefore a count of matches per target row:

SELECT o.id, count(*) FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY o.id HAVING count(*) > 1;

It must return nothing. DELETE ... USING is the same shape for deletes, with the same rehearsal.

Deleting in batches

DELETE FROM events WHERE created_at < '2024-01-01' on a table with 200 million old rows will run for an hour, hold locks the whole time, and generate a transaction log the size of the table. Delete in slices instead:

DELETE FROM events
WHERE id IN (SELECT id FROM events WHERE created_at < '2024-01-01' LIMIT 10000);

in a loop until the count is zero. Each slice commits quickly, other users get a turn between slices, and a mistake discovered after the third slice has cost thirty thousand rows rather than two hundred million. The partitioning lesson in the performance module shows the version that costs nothing: dropping a whole partition of old rows in one metadata operation.

Tools that help, and one that does not

DBeaver, which is free, can be set to ask for confirmation before running an UPDATE or DELETE without a WHERE (“Confirm dangerous query execution”). Turn that on. pgAdmin has no such check, but its Query Tool can run with Auto commit switched off, so a write waits for your COMMIT. psql has no such setting; it is the tool that trusts you, and it is the one usually connected to production. Some teams alias psql to open with --set AUTOCOMMIT=off, so that every write is inside a transaction until you say COMMIT, which is the subject of the next lesson and the strongest protection available.

The habit

Never type UPDATE or DELETE first. Type SELECT, read the rows, then edit the first word. The count in the rehearsal and the count in the write are the same number, or the write does not get committed.