Updating and Deleting Safely

Article · 2:00

UPDATE and DELETE, and the habits that stop one of them becoming an incident.

UPDATE changes columns in the rows a WHERE clause selects. DELETE removes them. Both do exactly what you asked, immediately, to every row that matches - and an UPDATE with no WHERE updates every row in the table. That is the single most common way a developer causes a production incident, and it is entirely preventable.

Three habits prevent it. Write the SELECT first. Run SELECT * FROM orders WHERE ..., look at the rows and the count, then change the word SELECT to UPDATE. Wrap it in a transaction. START TRANSACTION, run the statement, check the affected-row count, and only then COMMIT - ROLLBACK is available right up to that moment. Use safe-update mode, which Workbench enables by default: the server refuses an UPDATE or DELETE that has no WHERE on a key column. The instinct when it blocks you is to turn it off; the better instinct is to ask why your statement has no key in it.

Deleting in batches matters at scale. A DELETE of ten million rows is one enormous transaction that holds locks and fills the undo log; the same work in ten-thousand-row batches is ordinary. And deleting rows does not shrink the file - the space is reused but not returned, which is what OPTIMIZE TABLE in Module 11 is for.

-- 1. Write it as a SELECT and look at what comes back.
SELECT id, status, total FROM orders
WHERE status = 'pending' AND placed_at < '2026-01-01';

-- 2. Then change one word, inside a transaction.
START TRANSACTION;

UPDATE orders
SET status = 'cancelled', notes = 'Auto-cancelled: stale'
WHERE status = 'pending' AND placed_at < '2026-01-01';

SELECT ROW_COUNT();   -- is this the number you expected?
-- COMMIT;            -- only if it is
-- ROLLBACK;          -- if it is not

-- The statement that causes incidents. There is no WHERE.
-- UPDATE orders SET status = 'cancelled';

-- Safe-update mode refuses an UPDATE or DELETE with no key in the WHERE.
-- SET SQL_SAFE_UPDATES = 1;

-- Deleting at scale: batches, not one transaction holding ten million rows.
DELETE FROM audit_log
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 10000;         -- repeat until ROW_COUNT() is 0

-- Updating from another table.
UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.notes = CONCAT('VIP: ', c.name)
WHERE c.city = 'London';