Part 4 of 5 · 2 min read
Deleting with a join condition, and transactions around changes
Removing rows based on another table, and protecting a multi-step change with a transaction.
Deleting with a join condition, and transactions around changes
Think of removing every form in one filing cabinet that belongs to a client whose file in a separate cabinet has already been marked closed.
In plain words: a DELETE's WHERE clause can reference another table through a subquery, deleting rows based on a condition that depends on data elsewhere. Wrapping several INSERT, UPDATE or DELETE statements between BEGIN and COMMIT lets the whole group be undone with ROLLBACK if something looks wrong, before any of it becomes permanent.
A worked example. Deleting order_items for orders placed by a specific, recently removed test customer.
The key lines.
BEGIN;
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 999);
DELETE FROM orders
WHERE customer_id = 999;
-- check the results look right before committing
ROLLBACK; -- or COMMIT; once confirmed correct
ROLLBACK
ROLLBACK undoes every change made since the current transaction's BEGIN, as if none of it had happened. It only works before COMMIT; once committed, a change is permanent and needs a new, separate statement to reverse.
Example: running ROLLBACK after the two DELETE statements above restores every deleted row, as long as COMMIT was never run.
Remember this
Running a risky multi-statement change inside BEGIN, checking the results with a SELECT, and only then choosing COMMIT or ROLLBACK, is the single most useful habit for avoiding permanent, irreversible mistakes.
Quick notes
- A single INSERT can supply multiple rows, separated by commas, in one statement.
- An UPDATE's SET clause can compute a new value from the row's own current values.
- A DELETE's WHERE clause can reference another table through a subquery.
- ROLLBACK undoes every change since BEGIN, but only before COMMIT makes them permanent.
- Testing a risky change inside a transaction, checking it, then deciding COMMIT or ROLLBACK, is the safest way to run it.