UPDATE and DELETE Without Tears

~12 min · queries, write

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The two most dangerous statements

UPDATE and DELETE will affect every row in the table if you forget the WHERE clause. This has cost real teams real money. The discipline is simple and worth memorising.

The four-step safe pattern

Open a transaction: BEGIN;
Write a SELECT with the same WHERE clause; verify the row count and sample.
Run the UPDATE/DELETE.
If everything looks right, COMMIT; — otherwise ROLLBACK;.

RETURNING shows you exactly what changed

Add RETURNING * (or specific columns) to UPDATE/DELETE to see the affected rows in the same round trip. This is invaluable for audit logs and "did the right thing happen?" sanity checks.

Code

UPDATE patterns·sql

-- Single row, single column
UPDATE users SET role = 'admin' WHERE id = 7;

-- Multiple columns at once
UPDATE products
SET price = price * 1.10,
    updated_at = now()
WHERE category = 'electronics';

-- UPDATE with subquery
UPDATE orders
SET status = 'vip-priority'
WHERE customer_id IN (
    SELECT id FROM customers WHERE lifetime_spend > 10000
);

DELETE patterns·sql

DELETE FROM sessions WHERE expires_at < now();

-- DELETE with join-shaped condition
DELETE FROM cart_items
WHERE user_id IN (
    SELECT id FROM users WHERE deleted_at IS NOT NULL
);

Safe pattern with transaction + RETURNING·sql

BEGIN;
SELECT id, name, role FROM users WHERE last_login < now() - INTERVAL '1 year';
-- 47 rows. Looks right.
DELETE FROM users
WHERE last_login < now() - INTERVAL '1 year'
RETURNING id, name;
-- Review the returned rows.
COMMIT;  -- or ROLLBACK; if anything looks off

External links

Exercise

Practice the four-step pattern: BEGIN, SELECT preview, UPDATE/DELETE with RETURNING, COMMIT. Run a destructive operation in a sandbox table; ROLLBACK without committing; confirm the data is unchanged.