Skip to content
Tutorials Center
My learning

Topics

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.

SQL
UPDATE products
SET price = price * 1.10
WHERE category_id = 1;

Try it

SQL Raising prices for one category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Several columns, a subquery and an UPDATE ... FROM ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 WHERE first. Run it as a SELECT, or do the update inside a transaction you can ROLLBACK.
  • 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.
  • NULL in WHERE: WHERE col = NULL matches nothing. Write IS NULL.
  • A scalar subquery in SET must return at most one row; returning no row stores NULL.
  • Assigning to NOT NULL columns a NULL fails, as does breaking CHECK, UNIQUE or foreign-key rules.
  • Triggers and cascades fire on update; ON UPDATE CASCADE propagates 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.