SQL Reference ยท Statements and Clauses
SQL UPDATE statement
Reference for SQL UPDATE: change column values in existing rows, filter with WHERE, compute from other columns, update from joins and avoid the missing-WHERE trap.
UPDATE changes the values of columns in rows that already exist. The SET clause lists the new values, and WHERE chooses which rows to touch. New values may be literals, expressions that use the row's current values, or scalar subqueries.
Without a WHERE clause, UPDATE changes every row in the table. Write the WHERE first, run the same condition in a SELECT to see what it matches, and only then run the update.
UPDATE products SET price = price * 1.10 WHERE category_id = 1;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
UPDATE table_name SET column1 = expression1, column2 = expression2 [FROM other_tables] [WHERE condition];
| Part | Description |
|---|---|
table_name |
The table whose rows change. |
SET column = expression |
One assignment per column. Expressions see the old values of the row, so SET a = b, b = a swaps them. |
FROM |
Optional extra tables whose columns can be used in the assignments (see dialects). |
WHERE |
Which rows to update. Omitted means all rows. |
Result: the number of rows changed. Updating zero rows is not an error. A constraint violation fails the whole statement.
Dialect differences
| Dialect | Updating from another table |
|---|---|
| SQLite | UPDATE ... SET ... FROM other WHERE ... (3.33+). Also supports RETURNING and row values SET (a, b) = (1, 2). |
| PostgreSQL | UPDATE ... SET ... FROM other WHERE ..., plus RETURNING. |
| MySQL | UPDATE a JOIN b ON ... SET a.col = b.col WHERE .... Also supports ORDER BY and LIMIT on a single-table update. |
| SQL Server | UPDATE a SET a.col = b.col FROM a JOIN b ON ..., plus OUTPUT. UPDATE TOP (n) limits rows. |
| Oracle | No join form; use a correlated subquery in SET, or MERGE. |
-- MySQL UPDATE employees AS e JOIN departments AS d ON d.id = e.department_id SET e.email = CONCAT(LOWER(e.last_name), '@company.example') WHERE e.email IS NULL; -- SQL Server UPDATE e SET e.email = LOWER(e.last_name) + '@company.example' FROM employees AS e JOIN departments AS d ON d.id = e.department_id WHERE e.email IS NULL;
Usage notes
- Check the
WHEREfirst. Run it as aSELECT, or do the update inside a transaction you canROLLBACK. - Use relative updates (
SET stock = stock - 1) instead of reading a value into your program and writing it back; they are atomic and avoid lost updates. NULLinWHERE:WHERE col = NULLmatches nothing. WriteIS NULL.- A scalar subquery in
SETmust return at most one row; returning no row storesNULL. - Assigning to
NOT NULLcolumns aNULLfails, as does breakingCHECK,UNIQUEor foreign-key rules. - Triggers and cascades fire on update;
ON UPDATE CASCADEpropagates a changed key. - Do not update the primary key unless you must; it usually breaks references.
- Big updates are faster in batches, and indexes on the changed columns slow them down.
Related
INSERT INTO,DELETE, UPSERT,MERGE,RETURNING,WHERE- Lessons: UPDATE, Transactions