How do you make sure an UPDATE's WHERE clause targets exactly the rows you intend?
answer
- Develop it as a query first
- Keep the predicate byte-identical when you convert
- You should know the row count before you run it
- A comparison against NULL is not true
- Absent WHERE means all rows, not no rows
basics
~20 sRun the predicate as a SELECT with the same FROM and WHERE first, count and inspect the rows, then paste the identical predicate into the UPDATE and reconcile the affected-row count against that expected number.
solid answer
~50 sWrite the `SELECT` first. `SELECT * FROM orders WHERE status = 'pending' AND created_on < DATE '2026-01-01'` shows you exactly the rows the same predicate will target, and the count becomes your expected affected-row count. Then swap `SELECT *` for `UPDATE orders SET status = 'expired'` leaving the `WHERE` byte-identical, and compare the reported row count with what you expected — a mismatch means the predicate changed or the data moved under you. Two language points make this reliable. Targeting is decided once: the `WHERE` is evaluated against the table as of the start of the statement, so an update whose predicate tests a column it also sets — `SET tier = 'gold' WHERE tier = 'silver'` — cannot cascade over rows it has already changed. And a missing `WHERE` is not an error: it targets every row, so treat its absence as a deliberate, reviewed choice.
code
sql · 12 lines-- Preview: same FROM, same WHERE, and show both the old value and the new one
SELECT id, status AS current_status, 'expired' AS new_status, created_on
FROM orders
WHERE status = 'pending'
AND created_on < DATE '2026-01-01';
-- Convert: SET changes, WHERE stays byte-identical
UPDATE orders
SET status = 'expired',
expired_on = CURRENT_DATE
WHERE status = 'pending'
AND created_on < DATE '2026-01-01';go deeper
Write the SELECT with the intended WHERE first, look at the rows, then turn it into the UPDATE without touching the predicate. Never run an UPDATE without a WHERE unless you mean every row.
Explain that targeting is fixed against the table as of statement start, so an update cannot cascade over rows it just wrote, and know why NULL-bearing predicates and NOT IN quietly change the target set.
Demonstrate reconciliation: state the expected row count before running, compare it with the reported count afterwards, prefer EXISTS for cross-table targeting, and require the same discipline of application code that updates by key.
Set the standard for data-changing scripts: a stated expected row count, a preview query kept next to the statement, checked affected-row counts in application write paths, and review that treats an unqualified WHERE as a defect until justified.
## Two clauses, two jobs An UPDATE splits cleanly: `SET` decides **what** each targeted row becomes, `WHERE` decides **which** rows are targeted. Most damaging update bugs are targeting bugs, not value bugs — the new value was right, it just went to the wrong rows, or to all of them. ## Preview with the identical predicate The single most valuable habit is to develop the statement as a `SELECT` and convert it: ```sql -- 1. Preview SELECT id, status, created_on FROM orders WHERE status = 'pending' AND created_on < DATE '2026-01-01'; -- 2. Convert, leaving the WHERE untouched UPDATE orders SET status = 'expired', expired_on = CURRENT_DATE WHERE status = 'pending' AND created_on < DATE '2026-01-01'; ``` The preview gives you two things: your eyes on actual rows, and a number. That number is the expected affected-row count. If the UPDATE reports something different, stop and find out why before moving on. Note that engines differ on whether a row updated to a value identical to its current one is reported as affected, so make the preview count the authority and treat a difference as a question, not proof of a bug. It also helps to include, in the preview's select list, both the column you are about to overwrite and the value it will receive — that turns "which rows" and "what value" into one reviewable result set. ## Predicates that pull in another table When "which rows" depends on a second table, the portable predicate is a correlated `EXISTS`: ```sql UPDATE customers c SET vip = 'Y' WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000); ``` `EXISTS` is preferable to `IN` here because it is immune to NULLs in the compared columns, and its negation `NOT EXISTS` behaves predictably where `NOT IN` does not: a single NULL from the subquery makes `NOT IN` false for every row, silently updating nothing. The `EXISTS` subquery is a *filter*; it never contributes a value to `SET`. ## Targeting is fixed at statement start A question that catches people out: does `UPDATE members SET tier = 'gold' WHERE tier = 'silver'` loop, re-examining rows it has just promoted? No. SQL is set-oriented, not iterative: the statement determines its target set from the table as it stood when the statement began, and rows it writes do not re-enter that set. Every row that was silver becomes gold, exactly once, and the statement terminates. The same principle explains why `SET n = n + 1 WHERE n < 100` increments each qualifying row once rather than counting up to 100. ## Common targeting mistakes **The missing WHERE.** Legal, silent, and catastrophic. Some clients offer a safe-update mode that refuses an unqualified UPDATE; where it exists it is worth enabling, but the discipline of writing `WHERE` first is the real defence. **Predicates against nullable columns.** `WHERE cancelled_on <> CURRENT_DATE` does not match rows where `cancelled_on IS NULL`, because a comparison with NULL is unknown, not true. If NULL rows should be included, say so: `WHERE cancelled_on IS NULL OR cancelled_on <> CURRENT_DATE`. **Predicates on the column being set, written loosely.** `SET status = 'expired' WHERE status <> 'expired'` is a re-runnable, idempotent shape and often the one you want; `WHERE status = 'pending'` is narrower and expresses a specific transition. Choose consciously — the two behave very differently when the script is run twice. **Ambiguity from a broad key.** Updating by a business attribute (`WHERE email = ...`) when a unique key exists (`WHERE id = ...`) risks hitting more rows than intended if the attribute is not unique. Target by the narrowest reliable identifier available. ## Reconciling afterwards Every engine returns the number of rows an UPDATE affected, and every client library exposes it. Use it: an application that issues an update by primary key and does not check that it affected one row cannot distinguish "updated" from "the row was not there". In an ad-hoc script, compare the number against the preview count and against your own expectation stated *before* running it — writing the expected number down first is what turns the check into a real one.
- Does UPDATE members SET tier = 'gold' WHERE tier = 'silver' ever reprocess a row it has already promoted?No. The target set is fixed by evaluating the WHERE against the table as it stood at the start of the statement, and rows the statement writes do not re-enter that set. SQL is set-oriented rather than iterative, which is also why SET n = n + 1 WHERE n < 100 increments each qualifying row once instead of counting up.
- Why is WHERE NOT EXISTS safer than WHERE col NOT IN (subquery) for excluding rows?If the subquery returns even one NULL, NOT IN evaluates to unknown for every row and the statement updates nothing — silently. NOT EXISTS asks only whether a matching row was found, so NULLs in the compared columns cannot flip the whole predicate. Prefer it whenever the subquery's column is nullable.
- What should an application do with the affected-row count of an update by primary key?Check it. One means the row existed and was updated; zero means the row was absent or, on some engines, that the new values equalled the old — either way the caller's assumption is wrong and should surface as an error rather than a silent success. More than one means the predicate is not on a unique key.
The WHERE clause is the address on an envelope and SET is the contents. Nobody proofreads the contents of a letter that went to every address in the city; check the address first.
saying these in an interview costs you the question
- Assuming an UPDATE without WHERE is rejected
- Thinking an update loops over rows it just changed
- Using NOT IN against a nullable column to exclude rows
- Ignoring the affected-row count returned by the statement
- Believing a <> comparison matches NULL rows