In UPDATE employees SET salary = bonus, bonus = salary, which values do the right-hand sides read?

## Simultaneous assignment The mental model for a standard-conforming UPDATE is: for each qualifying row, take a snapshot of the row's current values, evaluate every expression in the `SET` list against that snapshot, then write all the results into the row in one step. Assignments in the list do not run one after another in the sense of a programming language's statement sequence — they are **simultaneous**, like a tuple assignment. That single rule explains all the interesting cases: ```sql -- Swaps the two columns UPDATE employees SET salary = bonus, bonus = salary WHERE id = 5; -- Increments once, not until some limit UPDATE counters SET n = n + 1 WHERE id = 1; -- billing gets the OLD shipping, shipping gets the OLD billing UPDATE addresses SET billing = shipping, shipping = billing WHERE id = 9; ``` Without the rule, `SET n = n + 1` would be paradoxical: the new `n` is defined in terms of `n`, so which `n`? The answer is always "the one stored before the statement began". Likewise the swap needs no temporary column, which is a genuine convenience compared with the two-statement version that would need a scratch value. ## A later assignment cannot see an earlier one The corollary trips people up. This does not carry the incremented value across: ```sql UPDATE t SET col1 = col1 + 1, col2 = col1; -- col2 gets the OLD col1 (standard) ``` If you want `col2` to end up equal to the *new* `col1`, you must spell the new value out again: `SET col1 = col1 + 1, col2 = col1 + 1`. Repeating the expression is not elegant, but it is unambiguous and it is portable, which matters more. ## The portability trap This is one of the places where a major engine genuinely diverges from the standard, so it is worth stating precisely rather than hand-waving. MySQL's documentation says single-table UPDATE assignments are generally evaluated left to right, which means a later assignment there *does* see the value written by an earlier one in the same statement — `SET col1 = col1 + 1, col2 = col1` leaves `col2` equal to the incremented `col1`, and the classic swap `SET a = b, b = a` leaves both columns holding the original `b`. PostgreSQL and other engines that follow the standard produce the swap. The practical rule for portable code: **never write a SET list where one assignment's correctness depends on whether another assignment has already happened.** If you find yourself reasoning about assignment order, restructure. Either repeat the expression, or stage the value in a subquery or a CTE feeding the statement, or split the work across statements where a well-defined order is what you actually want. ## Where the values come from is separate from which rows change Keep two questions apart. *Which* rows the statement touches is decided by the `WHERE` clause, evaluated against the table as it stood when the statement started; the statement does not re-examine rows it has already written, so `UPDATE members SET tier = 'gold' WHERE tier = 'silver'` cannot cascade. *What* each touched row gets is decided by the `SET` expressions, evaluated against that row's pre-update values. Both halves share the same principle: a statement sees a stable input image of the data and produces one result image. ## Interaction with other row expressions The pre-update rule applies to every expression in the list, not just bare column references. A `CASE` in the `SET` list tests old values: ```sql UPDATE accounts SET balance = balance - 50, status = CASE WHEN balance - 50 < 0 THEN 'overdrawn' ELSE status END WHERE id = 3; ``` Notice that the `CASE` has to recompute `balance - 50` itself, because it cannot observe the new `balance` written by the first assignment. That repetition is the signature of the rule in practice. The same holds for a scalar subquery in the `SET` list that correlates back to the target row: its correlation reads the row's pre-update column values. ## What to say in an interview State the rule ("all right-hand sides read the row as it was before the statement; assignments are simultaneous"), give the swap as the demonstration, give `SET n = n + 1` as the everyday consequence, then add the honest caveat that at least one major engine evaluates assignments left to right and that you therefore never write a SET list that depends on order.