skip to content

What does the row-assignment form UPDATE t SET (a, b) = (SELECT x, y ...) give you?

level: seniorimportance: nice to knowfreq 32%

answer

  1. Count how many times the lookup is written
  2. The left side can be a parenthesised list
  3. Mapping is positional, not by name
  4. Empty subquery nulls every listed column
  5. Not every engine accepts it

basics

~20 s

Row assignment sets several columns from a single subquery: one lookup supplies all the targets, instead of repeating the same correlated subquery once per column. Engine support varies, so confirm it before relying on it.

solid answer

~50 s

Standard SQL lets the left side of an assignment be a **list of columns** and the right side a subquery returning that many columns: `UPDATE t SET (city, country) = (SELECT a.city, a.country FROM addresses a WHERE a.id = t.address_id)`. Written the ordinary way you would repeat the whole correlated subquery once per target column, which duplicates the predicate, risks the two copies drifting apart, and expresses one lookup as several. The row form states the lookup once. The subquery must return at most one row and exactly as many columns as the target list, in matching order and compatible types; no matching row assigns `NULL` to every listed column, so the same unmatched-row guard applies as for a single-column correlated update. Support differs across engines — PostgreSQL and Oracle accept the form, MySQL does not — so check your engine before using it in portable code.

code

sql · 5 lines
sql
-- Repeating the same correlated lookup once per column
UPDATE customers c
   SET city    = (SELECT a.city    FROM addresses a WHERE a.id = c.address_id),
       country = (SELECT a.country FROM addresses a WHERE a.id = c.address_id)
 WHERE EXISTS (SELECT 1 FROM addresses a WHERE a.id = c.address_id);

go deeper

for a junior

Know that a SET target can be a parenthesised column list fed by one subquery returning the same number of columns, and that support is not universal.

for a middle

Explain what it buys — the correlated lookup written once instead of once per column — and state the rules: positional matching, at most one row, all-NULL on no match.

for a senior

Show the review checks you would run: unique source key per target row, column order read side by side, an explicit decision about unmatched rows, and a portability check before it lands in shared code.

for a principal

Decide the house rule: readability gains from a partially adopted standard feature are worth having on a single known engine, but a codebase that must span engines needs one agreed portable spelling for multi-column backfills.

## The problem it solves When several columns of a target row come from the same source row, the plain assignment list forces you to write the lookup once per column: ```sql UPDATE customers c SET city = (SELECT a.city FROM addresses a WHERE a.id = c.address_id), country = (SELECT a.country FROM addresses a WHERE a.id = c.address_id) WHERE EXISTS (SELECT 1 FROM addresses a WHERE a.id = c.address_id); ``` The same correlation predicate now appears three times. That is a maintenance hazard — change the join key and you must change every copy — and it expresses one conceptual lookup as three separate ones. ## The row-assignment form The standard allows a parenthesised column list on the left and a subquery on the right: ```sql UPDATE customers c SET (city, country) = (SELECT a.city, a.country FROM addresses a WHERE a.id = c.address_id) WHERE EXISTS (SELECT 1 FROM addresses a WHERE a.id = c.address_id); ``` The rules are straightforward: - The subquery must produce **exactly as many columns** as the target list names, in the same order. - Types must be assignment-compatible, column by column. - The subquery must return **at most one row**; more than one is a runtime cardinality error, just as with a scalar subquery. - If it returns **no** row, all the listed target columns are assigned `NULL` — the whole row-value is null, not "nothing happens". That last point matters: the row form does not rescue you from the unmatched-row problem. You still guard with `WHERE EXISTS` (or accept the NULLs deliberately). What it removes is the *duplication of the SET-side lookup*, not the need for a targeting predicate. ## Portability This is one of the parts of the standard with uneven adoption. PostgreSQL and Oracle accept the multi-column assignment form; MySQL does not. For other engines, check the documentation rather than assuming. If the form is unavailable, your portable options are to repeat the subquery per column, or to restructure the statement — some engines offer a join-style update or `MERGE`, which name the source once by construction. Because of that unevenness, treat the row form as a readability win on a known engine rather than as a default idiom in code that must run everywhere. ## Related row-value syntax The same row-constructor idea appears elsewhere in SQL and it is worth connecting them in an interview: `(a, b) = (SELECT x, y ...)` in a predicate compares row values, and `(a, b) IN (SELECT x, y ...)` tests row membership. The `UPDATE` form is the assignment counterpart of the same concept — SQL treating a tuple of values as one thing. Engines that support row values in predicates do not necessarily support them as assignment targets, so the two must be checked separately. ## Cardinality and correctness checklist Before shipping a row-assignment update, verify three things: 1. **At most one source row per target row.** Run the source query grouped by the correlation key and confirm no key appears twice; otherwise the statement aborts, possibly only in production where the duplicate exists. 2. **Column order and types.** The mapping is positional, not by name. Swapping two same-typed columns — `(city, country)` against `SELECT a.country, a.city` — is silent data corruption that no engine can catch for you. Read the pair side by side. 3. **Unmatched rows.** Decide explicitly whether they should be excluded by a guard or genuinely set to all-NULL. ## When it is genuinely the right tool It shines in backfills and data-repair scripts, where one source table supplies several denormalised columns per target row and the statement is read by a reviewer under time pressure. One lookup written once is much easier to check than three copies of the same predicate. It is less compelling when the columns come from different sources, when the engine does not support it, or when a `MERGE` or join-style update already expresses the operation more directly.

  • What happens if the subquery in a row assignment returns no rows?
    Every column in the target list is assigned NULL — the same unmatched-row wipe as a single-column correlated update, applied to all of them at once. The row form is about writing the lookup once, not about guarding rows, so you still need a WHERE EXISTS guard or a deliberate decision to accept the NULLs.
  • How are the subquery's columns matched to the target column list?
    Positionally, left to right, with assignment-compatible types required pair by pair. Names are irrelevant, so writing SET (city, country) = (SELECT a.country, a.city ...) is accepted and silently swaps the values. Read the two lists side by side in review; no engine can detect that mistake for you.

saying these in an interview costs you the question

  • Assuming columns match by name rather than by position
  • Thinking the row form guards against unmatched rows
  • Claiming every engine supports multi-column assignment
  • Expecting a multi-row subquery to pick some row instead of failing
  • Confusing it with INSERT's VALUES syntax

context

resolving links…