A nightly job suddenly fails with "more than one row returned by a subquery" — how do you fix it?

## Read the error as a statement about data "More than one row returned by a subquery used as an expression" is a **cardinality violation**: a subquery in a value position returned two or more rows. Nothing about it is a syntax problem, and nothing about it depends on the plan. The query has been correct-looking all along; what changed is that a lookup the author believed produced one row now produces several. That framing matters because the instinct under time pressure is to edit the query until the error stops. Editing until it stops is how a correct failure becomes an incorrect result. ## Step one: isolate and quantify Extract the subquery and run it on its own. Then ask the source table which keys duplicated: ```sql -- which lookup keys now have more than one row? SELECT region, COUNT(*) FROM offices GROUP BY region HAVING COUNT(*) > 1; ``` This answers three things at once: how widespread the duplication is, whether it is one bad row or a structural change, and whether the duplicates are meaningfully different (two live offices) or accidental (a re-import that inserted a second copy). The error message names the statement, never the offending key, so this step is not optional. ## Step two: recover the intent With the data in hand, decide what the query *meant*. There are only a few honest answers, and each has a different fix. **It meant "any of these".** The author wrote `=` out of habit where membership was intended. The predicate becomes `IN`: ```sql SELECT * FROM orders WHERE office_id IN (SELECT id FROM offices WHERE region = 'EU'); ``` This changes the result — more rows can match — so it is only right when set semantics were the intent. **It meant one specific row, and the predicate was under-specified.** The duplicates are distinguishable, and the inner query needs the distinguishing condition: a status, an effective date, a headquarters flag, a primary-key equality. ```sql SELECT * FROM orders WHERE office_id = (SELECT id FROM offices WHERE region = 'EU' AND is_headquarters = TRUE); ``` This is the best outcome, because the resulting query encodes the rule that was previously only in someone's head. **It meant "collapse them, I do not care which".** Sometimes that is genuinely true — the newest, the largest, the first. Say it with an aggregate, which returns exactly one row by construction and is deterministic: ```sql SELECT * FROM orders WHERE office_id = (SELECT MAX(id) FROM offices WHERE region = 'EU'); ``` **It meant a row per match.** If downstream really wants one output row per matching lookup row, the subquery was the wrong construct and a join is the right one. ## Step three: the fix to refuse Appending `LIMIT 1` — or `FETCH FIRST 1 ROW ONLY` — makes the error disappear immediately, which is why it is so tempting during an incident. It is the worst option available. Without an `ORDER BY`, no engine promises which row you receive; with one, ties are still broken arbitrarily. The query now returns a plausible number that may differ between runs, and the data problem that caused the incident is no longer visible to anyone. If you must ship it to unblock a deadline, ship it with the ordering that makes it deterministic and a ticket to resolve the semantics. ## Step four: move the assumption into the schema The deeper defect is that a uniqueness rule lived in query text. `= (SELECT ... WHERE email = ?)` is an assertion that emails are unique, made in a place where nothing enforces it. If the rule is real, it belongs on the table as a uniqueness constraint, so the second row is rejected at insert time by the component that created it — hours before the nightly job runs, and with a message naming the row rather than the report. If the rule is *not* real — duplicates are legitimate — then every query using `=` against that column is latent breakage, and the sweep is worth doing at once rather than one incident at a time. ## Why this failure mode is the good one It is worth saying out loud in an interview: this error is the friendly half of the scalar-subquery contract. Too many rows raises, stops the job, and points at the statement. Too few rows returns NULL, propagates silently through arithmetic and predicates, and produces a report somebody believes. A team that has learned to fix cardinality errors by suppressing them is converting the loud failure into the quiet one, which is exactly the wrong direction.