How do you set several columns in one UPDATE, and why does SET a = 1 AND b = 2 fail?
answer
- Only one SET keyword per statement
- Assignments are separated by something punctuational
- AND belongs in WHERE, not in SET
- The parser swallows everything after = as one expression
basics
~20 sUse one SET keyword with comma-separated assignments: SET price = 10, qty = 0. AND is a boolean operator, not a separator, so SET price = 10 AND qty = 0 assigns price the result of an expression and never touches qty.
solid answer
~40 sAn UPDATE has exactly one `SET` clause, and it takes a comma-separated list of `column = expression` assignments: `UPDATE products SET price = 10, qty = 0, updated_on = CURRENT_DATE WHERE id = 7;`. Writing `SET price = 10 AND qty = 0` is the classic beginner error: the parser reads `price` as the only assignment target and everything after `=` as a single expression, `10 AND (qty = 0)`. A strictly typed engine rejects that as a type error; a loosely typed one can silently store a boolean-ish 0 or 1 into `price`, and `qty` is never modified at all. The right-hand side of each assignment can be any expression the engine can evaluate for that row — a literal, a column, an arithmetic expression, `CURRENT_DATE`, a `CASE`, or a scalar subquery.
code
sql · 11 lines-- Wrong: assigns price the value of the expression 10 AND (qty = 0); qty is untouched
UPDATE products
SET price = 10 AND qty = 0
WHERE id = 7;
-- Right: one SET, comma-separated assignments
UPDATE products
SET price = 10,
qty = 0,
updated_on = CURRENT_DATE
WHERE id = 7;go deeper
Memorise the shape: UPDATE table SET col = expr, col = expr WHERE cond. One SET, commas between assignments, and never AND.
Explain what the parser actually does with SET a = 1 AND b = 2 — one assignment whose value is a boolean expression — and why a loosely typed engine corrupts data silently instead of erroring.
Be ready to say why you fold every change to a row into a single statement: fewer round trips, one trigger firing, no half-updated row visible in between, and one place that documents the operation.
Own the review rule: a statement that changes a row must be reviewable in one place, and lint or code review should reject multi-statement row edits and unqualified WHERE-less updates before they reach production.
## The shape of the statement The standard single-table UPDATE has three parts: ```sql UPDATE products SET price = 10, qty = 0, updated_on = CURRENT_DATE WHERE id = 7; ``` `UPDATE <table>` names the target, `SET` lists what changes, and the optional `WHERE` decides which rows change. There is exactly **one** `SET` keyword per statement no matter how many columns you touch; repeating `SET` (`SET a = 1 SET b = 2`) is a syntax error. ## SET is a comma-separated assignment list Each element is `column_name = expression`. The column name is bare — you do not qualify it with the table name or alias on the left of the `=`, because there is only one target table and the parser already knows it. (Qualified names on the *right*, such as `SET total = o.price * o.qty`, are fine if you gave the target an alias.) The expression can be: - a literal: `SET status = 'shipped'` - another column of the same row: `SET billing_city = shipping_city` - an arithmetic or string expression over the row: `SET price = price * 1.10` - a function or niladic value: `SET updated_on = CURRENT_DATE` - a conditional: `SET tier = CASE WHEN points > 1000 THEN 'gold' ELSE 'silver' END` - a scalar subquery: `SET city = (SELECT city FROM addresses a WHERE a.id = p.address_id)` All of those live in one comma-separated list, so a single statement can rewrite an entire row. ## Why AND is not a separator `AND` is the boolean conjunction operator from `WHERE`, and beginners transplant it into `SET` because both clauses "list things". The grammar does not agree. In `SET price = 10 AND qty = 0`, the parser takes `price` as the assignment target, `=` as the assignment operator, and then greedily consumes the rest as **one expression**: `10 AND (qty = 0)` (comparison binds tighter than `AND`). What happens next depends on how strict the engine's type system is. An engine that refuses to mix an integer and a boolean raises a type error, which is the lucky outcome — you see the mistake immediately. An engine with looser typing evaluates the conjunction to a truth value, coerces it, and stores `0` or `1` in `price`. Nothing warns you, and `qty` is left exactly as it was. That is a silent data-corruption bug on a production table, which is why interviewers still ask about it. The same reasoning explains why you cannot write `SET price = 10, WHERE id = 7` with a stray comma, or `SET (price, qty) = 10, 0` — the assignment list has a fixed shape. ## One statement or several? Two separate statements against the same row (`UPDATE ... SET price = 10 WHERE id = 7;` then `UPDATE ... SET qty = 0 WHERE id = 7;`) produce the same final data, but they cost two round trips, re-evaluate the `WHERE` twice, and fire row-level triggers twice. Prefer one statement per row-change. It is also easier to read: the assignment list documents, in one place, everything this operation does to the row. ## Practical notes Omitting `WHERE` is legal and updates **every** row in the table — the engine will not ask you whether you meant it. Treat a missing `WHERE` as a deliberate decision, not an oversight. Assigning a column its current value is legal and is not an error; whether the engine reports such a row in the affected-row count varies, so do not build logic on that count when the new value may equal the old one. Finally, an assignment must respect the column's type and any constraints: setting a `NOT NULL` column to `NULL`, or a foreign-key column to a value with no parent row, fails the whole statement. An UPDATE is all-or-nothing — if one row's assignment violates a constraint, no row in the statement is left half-changed.
- Can you qualify the column name on the left of an assignment, as in SET p.price = 10?No. The left side of an assignment is a bare column name of the target table; the standard does not allow qualifying it, and most engines reject it. Qualification is only meaningful on the right-hand side, where an alias distinguishes the target's columns from a subquery's — for example `SET total = p.price * p.qty`.
- Is it valid to update two columns with two separate UPDATE statements instead of one?Valid, and the end data is identical, but it costs two round trips, re-evaluates the WHERE twice and fires row triggers twice. It also opens a window in which the row is half-updated and visible to other readers. Put every change to a row in one statement's SET list.
- What happens if one assignment in the SET list violates a constraint?The whole statement fails and no row is changed — a statement is atomic. You never end up with some columns of the row updated and others not, nor with earlier rows written and later ones skipped.
SET is a shopping list, not a sentence: items are separated by commas. Joining them with "and" turns the whole list into one item whose value happens to be true or false.
saying these in an interview costs you the question
- Thinking AND separates assignments the way it separates predicates
- Writing SET twice in one statement
- Believing multi-column updates need one statement per column
- Assuming a missing WHERE is rejected rather than updating everything
- Qualifying the target column on the left of the assignment