Why does an UPDATE whose SET reads a correlated subquery set unmatched rows to NULL?

## The pattern A correlated update pulls each target row's new value out of another table: ```sql UPDATE employees e SET dept_name = (SELECT d.name FROM departments d WHERE d.id = e.dept_id); ``` The subquery references `e.dept_id`, a column of the row being updated, which is what makes it *correlated*: conceptually it is evaluated once per target row, with that row's values plugged in. ## Where the damage comes from The statement above has no `WHERE`, so every row of `employees` is a target. For an employee whose `dept_id` matches a department, the subquery returns that name. For an employee whose `dept_id` is `NULL`, or points at a department that no longer exists, the subquery finds no row — and a scalar subquery over an empty result is defined to produce `NULL`, not "no value" and not "leave the column alone". SQL has no notion of an assignment that declines to happen. So the engine dutifully writes `NULL` over whatever `dept_name` held. The realisation to state out loud in an interview is that **the SET subquery cannot filter rows**. Its job is to produce a value; row selection is the `WHERE` clause's job alone. People write the correlation once, in `SET`, and unconsciously expect it to do both jobs. ## Fix one: repeat the correlation in WHERE EXISTS ```sql UPDATE employees e SET dept_name = (SELECT d.name FROM departments d WHERE d.id = e.dept_id) WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id); ``` Now only employees with a matching department are targeted; the rest keep their old value and are not counted as updated. The duplication of the predicate is ugly but it is the portable ANSI answer, and the two copies must be kept in step — a mismatch between them reintroduces the bug. `EXISTS` is the right existence test here rather than `IN`, because `IN` against a column that can contain `NULL` brings three-valued logic into the picture; `EXISTS` asks only whether a row was found. ## Fix two: COALESCE the old value back in ```sql UPDATE employees e SET dept_name = COALESCE((SELECT d.name FROM departments d WHERE d.id = e.dept_id), e.dept_name); ``` This writes the old value back when there is no match, so nothing is lost. It is shorter, but it has two drawbacks: every row is still targeted, so the affected-row count no longer tells you how many rows genuinely matched, and it cannot distinguish "no matching source row" from "the source row's value really is NULL" — both cases keep the old value. Choose it when the source column is `NOT NULL` and you want the terser statement; choose `EXISTS` when you want the statement to touch only what it should. ## When the subquery matches more than one row A second failure mode of the same pattern: if the correlation is not unique — say two departments share an `id` because the join key is wrong — the scalar subquery returns more than one row and the statement fails at runtime. That is the good outcome, because the alternative (silently picking one) would be non-deterministic. Before writing a correlated update, confirm the correlation predicate genuinely identifies at most one source row; if the source may legitimately have several rows, aggregate deliberately (`SELECT MAX(...)`, `SELECT SUM(...)`) so the intent is written down. ## Updating several columns from the same source Repeating the whole correlated subquery once per assigned column works, but restates the same lookup several times. The standard row-assignment form `SET (a, b) = (SELECT x, y FROM ... WHERE ...)` expresses the single lookup once; engine support for it varies, so check before relying on it. Some engines also offer a join-style update or `MERGE`, which express "only rows that matched" directly. ## A safe procedure 1. Write the correlation as a `SELECT` first: `SELECT e.id, e.dept_name, (SELECT d.name FROM departments d WHERE d.id = e.dept_id) AS new_name FROM employees e;` and eyeball the rows where the new value comes back `NULL`. 2. Decide explicitly what those rows should do — keep the old value, be excluded, or genuinely become `NULL`. 3. Encode that decision as `WHERE EXISTS`, `COALESCE`, or a deliberate no-op. 4. Compare the affected-row count with the count of rows you expected to match. The bug survives in the wild because the broken statement is shorter than the correct one and works perfectly on test data where every row happens to have a match.