You run an UPDATE by hand on production and the client answers Rows matched: 84520 (MySQL) or UPDATE 84520 (PostgreSQL). You expected about 300. The WHERE clause is missing, or it is there and does not say what you thought it said.
This is the oldest accident in hands-on database work, and it does not come from not knowing SQL. It comes from the gap between the statement you meant to run and the one you actually ran. This post closes that gap with three checks, in execution order: before executing the change, read it back as a sentence and run a verification SELECT. Then execute it in a transaction where rollback is supported, check the reported row count, and commit or roll back. A small tool helps with the first check.
How the WHERE clause goes missing
Most of these accidents happen between editing and executing:
- Partial execution. In a GUI client you select a range and run it, and the selection ends before the WHERE clause.
- Ran too early. You hit the run shortcut before adding the condition.
-
Debug leftovers.
WHERE 1=1stayed in; the real condition never arrived, and1=1is always true. -
Misread precedence. The WHERE clause exists, but
a = 1 OR b = 2 AND c = 3covers more rows than you pictured, because AND binds tighter than OR.
In every case you read the statement you meant, not the one on the screen. Attention does not fix that, because attention is what failed. Procedure does.
Check 1 (before execution): read the statement back as one sentence
This check is about meaning. Before running, say the statement in plain language: "set status to INACTIVE for every row in m_users whose last login is before 2024-01-01". If you cannot produce that sentence, do not run the statement yet.
You can do this by hand. To make it harder to skip, I use SQLMegane, a static browser app that parses the statement (an SQL parser producing an AST for MySQL, PostgreSQL and SQL Server) and writes the sentence for you, deterministically and without an LLM, plus warnings and a verification SELECT for the next check. SQL analysis stays in the browser: no install, no account, no server. A CLI uses the same core. Its actual output for a DELETE with no WHERE clause, unedited:
$ printf 'DELETE FROM m_users;' | node cli/sqlmegane.mjs --lang en --dialect mysql -
Dialect: mysql Statements: 1
--- #1 DELETE ---
DELETE: deletes ALL rows of `m_users`
⚠ No WHERE clause — every row is affected.
[DANGER] DELETE without a WHERE clause: No WHERE clause was found. This deletes every row in the table. Confirm that a full-table delete is intended.
[INFO] Not wrapped in a transaction: No transaction start was found before this destructive statement. Use an explicit transaction when your database and operation support rollback.
Verification SELECT: SELECT COUNT(*) FROM m_users;
The process exits with code 2, so the same command can gate a CI job or a pre-run hook: node cli/sqlmegane.mjs --lang en --dialect mysql planned.sql && mysql -h prod mydb < planned.sql never reaches the second half when a danger-level finding exists. By default, warning and info findings do not stop this command; use --fail-on warning to stop on warnings as well. The CLI does not execute or block SQL itself; the exit code is the whole interface. This batch example does not provide the interactive row-count check in check 3 below.
A leftover 1=1 is caught the same way ([DANGER] WHERE clause is always true). For TRUNCATE TABLE m_users; the summary says "immediately removes ALL rows from m_users (usually cannot be rolled back)", which is where a TRUNCATE-instead-of-DELETE slip gets noticed.
When the WHERE clause is fine, you get the sentence to compare with your intent:
UPDATE: updates rows in `m_users` where `last_login` < '2024-01-01', setting `status` = 'INACTIVE'
Verification SELECT: SELECT COUNT(*) FROM m_users WHERE last_login < '2024-01-01';
A count can match by coincidence while the meaning is off (OR precedence, a LEFT JOIN made equivalent to an INNER JOIN by a null-rejecting WHERE condition on the nullable side, NOT IN with a NULL in the list). The sentence can help you spot a mismatch before execution, but parser and rule limitations can produce an incorrect or incomplete reading.
Check 2 (before execution): run the same WHERE clause as a SELECT
Before the UPDATE or DELETE, put its WHERE clause under SELECT COUNT(*).
SELECT COUNT(*) FROM m_users WHERE last_login < '2024-01-01'; -- approximate number of rows the change would match
SELECT COUNT(*) FROM m_users; -- total rows (for DELETE)
The second line matters for DELETE: "rows to delete = total rows" is the worst case, and you only see it if you recorded both. Look at a few rows with SELECT * too, because a plausible count can still be the wrong rows. The count is an estimate, not a prediction of the affected-row count: LIMIT, joins, concurrent changes, triggers and how your client counts rows can make the numbers differ. Compare it with the affected-row count in check 3. On a large table COUNT(*) takes time; plan for it rather than skipping it.
One runbook line, "paste the verification SELECT result before running", is cheap and lowers the accident rate. Its weakness is transcription: you can copy the WHERE clause wrong.
Check 3 (at execution): run it in a transaction, look at the row count, then commit or roll back
Start a transaction, run the statement, stop, and read the affected-row count before you decide.
BEGIN; -- MySQL / PostgreSQL. SQL Server: BEGIN TRANSACTION. Oracle: no BEGIN needed (see below)
UPDATE m_users SET status = 'INACTIVE' WHERE last_login < '2024-01-01';
-- Stop here. Check the affected-row count on the same connection.
If the count and the other checks match your expectations, commit. Otherwise, roll back. Rollback undoes transactional changes that have not been committed; it does not cover non-transactional changes.
COMMIT;
ROLLBACK;
Keep COMMIT out of the block you execute; otherwise the change is final before you see the count.
What bites here, by database:
-
MySQL defaults to
autocommit=1, and psql autocommits too. With these default autocommit settings, a successful UPDATE or DELETE outside an explicit transaction is committed automatically. -
In MySQL, issuing
BEGINwhile a transaction is already open implicitly commits everything before it. So "just type BEGIN to be safe" is not safe; check that no transaction is open first. Non-transactional tables (MyISAM) cannot be rolled back at all. -
Oracle starts a transaction with the first DML. A bare
BEGINis a PL/SQL block, not a transaction start. Turn off client autocommit; in SQL*Plus,EXITCOMMITdefaults to ON, so a normal exit commits what you left uncommitted. -
TRUNCATEis notDELETE. On regular tables it commits implicitly in MySQL and Oracle and cannot be rolled back. PostgreSQL and SQL Server can roll it back only inside an uncommitted transaction. Before running TRUNCATE, confirm that a full-table removal is intended and whether rollback is available in your current database and transaction.
This check does not help with lock time during a large change, and it cannot undo a change that has already been committed.
What this does not cover
- None of these checks can tell whether your intended change is correct for the business. A sentence that matches your intent can still be wrong if your intent was wrong.
- The tool checks structure only. Statements that fail to parse fall back to basic checks, and a parser fallback does not establish compatibility with the selected database. Oracle and Generic modes use regex heuristics and do not generate an English summary. PL/SQL blocks are read to pull out the DML, but loops, branches and exception handling are not analyzed. PostgreSQL dollar quoting and Oracle-specific syntax such as
(+)andCONNECT BYare not parsed. - "No danger detected" does not mean safe. It means none of the implemented patterns matched.
- Whether a committed DELETE can be undone was decided before the accident, by your backup design (PITR from a base backup plus WAL or binlog, a delayed replica, or nothing). If it already happened, do not repair it with more UPDATE or DELETE. Record the time, the exact SQL and the row count, and hand them to whoever owns recovery.
Summary
| Check | When | What it checks | What it catches |
|---|---|---|---|
| Read it back as one sentence | Before execution | Meaning of the range |
1=1 leftovers, precedence, TRUNCATE vs DELETE, intent mismatch |
| Verification SELECT + total count | Before execution | Approximate rows matched | Missing WHERE, wrong condition, "delete = total" |
| Transaction + affected-row count | At execution | Reported rows, before commit | Unexpected row counts; transactional changes can be rolled back before commit |
If your team runs production SQL by hand from a runbook, add these three checks to it in this order, and check on a calm day that your backups and PITR actually work.
Disclosure: SQLMegane is designed, implemented and maintained by an AI assistant (Selene) under human supervision, and this article was written the same way. It is open source under MIT: GitHub.
If you have a pre-run routine that works, or a reason this tool would not fit your workflow, tell me in GitHub Discussions. One line is enough.
