UPDATE
Change existing rows — and the one clause standing between "some rows" and "every row."
Syntax
UPDATE table
SET column1 = value1, column2 = value2, ...
WHERE predicate;| Parameter | Type | Notes |
|---|---|---|
| SET* | column = expression, … | Which columns to change and their new values. Reference the row's own
current values freely: |
| WHERE | predicate | Optional only in syntax — omit it and every row in the table is updated. Always write it deliberately, never by accident of omission. |
Basic update
Loading editor…
No WHERE updates every row
Loading editor…
Every order became 'reviewed' — there was no WHERE to narrow it. This is
syntactically valid on every engine, which is exactly the danger: nothing
stops it from running. Run the equivalent SELECT ... WHERE ... first,
confirm the row count, then convert it to an UPDATE.
Updating from another table
Standard SQL doesn't have one syntax for this — it's genuinely different per engine:
-- Postgres: UPDATE ... FROM
UPDATE orders o
SET amount = amount * 0.9
FROM customers c
WHERE c.customer_id = o.customer_id AND c.country = 'CA';
-- MySQL: UPDATE with JOIN
UPDATE orders o
JOIN customers c ON c.customer_id = o.customer_id
SET o.amount = o.amount * 0.9
WHERE c.country = 'CA';
-- SQL Server: UPDATE ... FROM (same shape as Postgres)
UPDATE o
SET o.amount = o.amount * 0.9
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'CA';Loading editor…
(SQLite has no UPDATE ... FROM ... JOIN shorthand for this shape — a
subquery like above is the portable fallback that works everywhere.)
Getting the changed rows back
Loading editor…
Gotchas
- No
WHEREmeans every row — the single most common accidental-update cause. Test withSELECTfirst. SET col = col + 1uses the pre-update value, evaluated per row — safe for relative updates, but two concurrent updates to the same row are a race unless the transaction/isolation level prevents it.- Updating the join key itself can change which rows a multi-table
UPDATE ... FROMmatches mid-statement on some engines — avoid updating a column that's also part of the join condition.
- DELETE — same "no WHERE = everything" danger, more permanent.
- WHERE — the predicate rules are identical to a SELECT's WHERE.
- Rows missing in another table — the join patterns UPDATE ... FROM/JOIN are built on.
Safety checklist
- run the equivalent SELECT ... WHERE first and check the row count
- wrap in a transaction you can roll back until you've verified the result
- for cross-table updates, confirm the join can't match a row more than once (that silently picks one match arbitrarily)