SQL / INSERTING, UPDATING, AND DELETING ROWS
The always-check-your-WHERE discipline
Verify the WHERE clause of any UPDATE or DELETE by running it as a SELECT first, then confirm the affected-row count inside a transaction you can roll back.
What you will learn
- Predict the row count, then prove it with SELECT count(*) before you write anything
- Copy the verified predicate unchanged into the UPDATE or DELETE
- Read the UPDATE n / DELETE n tag inside BEGIN so ROLLBACK is still possible
- Catch NULL-blind and unparenthesised AND/OR predicates before they commit
Understanding The always-check-your-WHERE discipline
A WHERE clause in a SELECT is a question; the same clause in an UPDATE or DELETE is a command carried out without showing you what it hit. The server will void 40,000 invoices without hesitation if the predicate resolves that way, and the only feedback is a tag like UPDATE 40000 printed after the fact. The discipline is to reverse that order: decide how many rows should change, run the predicate as a SELECT to see whether the server agrees, and only then attach it to a write.
Predicates surprise people for structural reasons, not just careless ones. WHERE keeps a row only when the condition is TRUE, so a comparison against NULL, such as status <> 'void' on a row whose status is NULL, drops the row silently instead of raising an error. AND binds tighter than OR, so queue = 'billing' AND state = 'closed' OR state = 'archived' stops applying the queue test to archived rows. Both faults are invisible in the text of the statement and immediately visible in a row count, which is why the count is the thing you check.
That count is only useful while you can still act on it, and in autocommit mode the tag appears after the change is durable. So BEGIN before the write, compare the tag to your prediction, then COMMIT or ROLLBACK; a mismatch is not something to investigate later, it is a reason to undo now. One limit: a matching count proves only that the SELECT and the write read the data the same way, so if the predicate misreads your intent both will be wrong identically — look at the rows the SELECT returns, not only how many there are.
CREATE TABLE invoice (id int, customer text, status text);
INSERT INTO invoice VALUES
(1, 'acme', 'open'),
(2, 'acme', 'paid'),
(3, 'globex', 'open'),
(4, 'globex', 'open');
-- 1. Prediction: 2 globex rows are open. Ask the server before writing.
SELECT count(*) AS matched
FROM invoice
WHERE customer = 'globex' AND status = 'open';
-- 2. Same predicate, copied unchanged, inside a transaction.
BEGIN;
UPDATE invoice
SET status = 'void'
WHERE customer = 'globex' AND status = 'open';
-- 3. The tag says 2, which is what was predicted, so keep it.
COMMIT;
SELECT id, customer, status FROM invoice ORDER BY id;For an UPDATE or DELETE the WHERE clause fires blind, so prove the predicate with a SELECT and check the affected-row count while a ROLLBACK is still possible.
Worked examples
AND binds before OR
The same intent written with and without parentheses selects different row sets, which only a count reveals.
CREATE TABLE ticket (id int, queue text, state text);
INSERT INTO ticket VALUES
(1, 'billing', 'open'),
(2, 'billing', 'closed'),
(3, 'support', 'closed'),
(4, 'support', 'archived');
-- Intent: billing tickets that are closed or archived.
SELECT count(*) AS buggy
FROM ticket
WHERE queue = 'billing' AND state = 'closed' OR state = 'archived';
SELECT count(*) AS fixed
FROM ticket
WHERE queue = 'billing' AND (state = 'closed' OR state = 'archived');Example explained
Line 1AND is evaluated before OR, so the first predicate means (queue = 'billing' AND state = 'closed') OR state = 'archived'.
Line 2Row 4 belongs to the support queue but satisfies the dangling OR branch, so buggy counts it.
Line 3The parenthesised form keeps queue = 'billing' applied to both states and returns 1.
Line 4As two counts the difference is obvious; as a DELETE it would have removed another team's ticket.
A predicate that cannot see NULL
Shows why <> silently omits NULL rows and how IS DISTINCT FROM restores the rows you meant to hit.
CREATE TABLE subscriber (id int, email text, plan text);
INSERT INTO subscriber VALUES
(1, 'a@example.com', 'pro'),
(2, 'b@example.com', NULL),
(3, 'c@example.com', 'free');
SELECT count(*) AS not_pro
FROM subscriber
WHERE plan <> 'pro';
SELECT count(*) AS really_not_pro
FROM subscriber
WHERE plan IS DISTINCT FROM 'pro';Example explained
Line 1NULL <> 'pro' evaluates to NULL, not TRUE, and WHERE discards everything that is not TRUE.
Line 2Row 2 is therefore missing from the first count although its plan is plainly not 'pro'.
Line 3IS DISTINCT FROM compares NULL as a value and returns TRUE, giving the 2 rows intended.
Line 4An UPDATE built on the first predicate would report success while leaving row 2 untouched.
Rolling back a count that does not match
A DELETE whose reported count exceeds the prediction is undone before it becomes permanent.
CREATE TABLE session_log (id int, user_id int, expires_at date);
INSERT INTO session_log VALUES
(1, 7, '2026-01-05'),
(2, 7, '2026-06-30'),
(3, 9, '2026-01-05');
-- Intent: drop user 7's expired session. Prediction: 1 row.
BEGIN;
DELETE FROM session_log WHERE expires_at < '2026-02-01';
-- Tag says 2, prediction was 1: undo now, diagnose after.
ROLLBACK;
SELECT count(*) AS rows_left FROM session_log;Example explained
Line 1The predicate filters on expires_at only; the user_id = 7 half of the intent was never written.
Line 2DELETE 2 against a prediction of 1 is the entire signal, before knowing which row is extra.
Line 3ROLLBACK discards both deletions because the statement ran inside an open transaction.
Line 4The closing count of 3 proves nothing was lost; without BEGIN, row 3 would already be gone.
Important notes
MySQL prints 'Rows matched: 3 Changed: 1' because it skips writes that would not alter a value; compare your prediction with the matched number, not the changed one.
A wrapping transaction only saves statements that are transactional: in MySQL and Oracle, TRUNCATE and DDL commit on their own, so there is nothing left to roll back.
Common mistakes
Verifying with a SELECT and then adjusting the predicate while moving it into the DELETE, for example dropping AND status = 'open' during the edit, so the write hits rows the SELECT never displayed.
Peeking with SELECT ... LIMIT 20, seeing twenty rows and assuming that is the target; the DELETE has no LIMIT and removes every matching row, potentially thousands.
Trusting WHERE plan <> 'pro' or WHERE plan = NULL: no error is raised, the NULL rows are skipped, and the update looks successful while leaving behind exactly the rows it was written to fix.
Try it yourself
Change, predict, then run
Create orders(id, customer, status) with five rows, one of which has a NULL status, then write a predicate that counts every order that is not 'shipped' including the NULL one. Reuse that exact predicate in BEGIN; UPDATE orders SET status = 'review' ...; ROLLBACK; and confirm the reported row count (or SELECT changes() on SQLite) equals your count.
Open the SQL workspaceCheck your understanding
You run SELECT count(*) FROM invoice WHERE customer = 'acme' AND status <> 'void'; and get 4. The UPDATE with the same predicate then reports UPDATE 4. What has that agreement actually established?
- Nothing: a row-count tag is printed after the write, so it can never be compared with the SELECT.
- That the write touched exactly the rows the SELECT showed, while any acme invoice with a NULL status was skipped by both.
- That every acme invoice other than the void ones now carries the new status, NULL statuses included.
- That the predicate matches your intent, because the SELECT and the UPDATE agree on 4 rows.
Show answer
Matching counts show only that both statements read the data the same way. status <> 'void' yields NULL for a NULL status and WHERE keeps only TRUE rows, so those invoices were invisible to the SELECT and to the UPDATE alike; the option claiming NULL statuses were updated is therefore false. The option treating agreement as proof of intent is the tempting one, but a predicate can be consistently wrong in both places — writing status IS DISTINCT FROM 'void' is what changes the answer.