UPDATE

UPDATE changes values in existing rows: a price rise, an order moving from pending to shipped, a customer's new email. It's simple to write — and, without care, simple to apply to far more rows than you intended.

Updating one row

SQL

UPDATE orders
SET status = 'shipped'
WHERE id = 12;

SELECT id, status FROM orders WHERE id = 12;

Result 1 row

id	status
12	shipped

SET gives the new values; WHERE chooses the rows. Filtering by the primary key guarantees exactly one row changes.

Several columns at once

SQL

UPDATE customers
SET email = 'julia.santos@example.com',
    city = 'Porto'
WHERE id = 10;

SELECT name, email, city FROM customers WHERE id = 10;

Result 1 row

name	email	city
Julia Santos	julia.santos@example.com	Porto

Calculated updates

SQL

UPDATE products
SET price = round(price * 1.05, 2)
WHERE category = 'Books';

SELECT name, price FROM products WHERE category = 'Books';

Result 3 rows

name	price
The Patient Gardener	26.14
Learning SQL Step by Step	34.13
Night Sky Atlas	47.25

The new value can be based on the old one. Every matched row is updated using its own current values.

The missing WHERE

SQL

UPDATE products SET stock = 0;

SELECT COUNT(*) AS products_with_stock FROM products WHERE stock > 0;

Result 1 row

products_with_stock
0

Without WHERE, every row is updated. There's no undo — unless you're inside a transaction (lesson 39) or have a backup. Good habits:

Write and run the WHERE as a SELECT first to see which rows it matches.
Check the "rows affected" count your tool reports.
Use a transaction for anything risky, so you can roll back.

Updating with CASE

SQL

UPDATE employees
SET salary = CASE
    WHEN job_title LIKE '%Manager%' THEN salary + 3000
    WHEN hire_date < '2020-01-01' THEN salary + 2000
    ELSE salary + 1000
  END
WHERE department_id IS NOT NULL;

SELECT first_name, job_title, salary FROM employees WHERE department_id = 1;

Result 3 rows

first_name	job_title	salary
Rafael	Sales Manager	85000
Lucas	Sales Representative	53000
Sofia	Sales Representative	55000

One statement applies different changes to different rows — faster and safer than several separate updates.

Updating from another table

SQL

UPDATE products
SET stock = stock - (
  SELECT SUM(oi.quantity)
  FROM order_items AS oi
  JOIN orders AS o ON o.id = oi.order_id
  WHERE oi.product_id = products.id AND o.status = 'pending'
)
WHERE id IN (
  SELECT oi.product_id FROM order_items AS oi
  JOIN orders AS o ON o.id = oi.order_id
  WHERE o.status = 'pending'
);

SELECT id, name, stock FROM products WHERE id IN (2, 9, 10);

Result 3 rows

id	name	stock
2	Learning SQL Step by Step	22
9	Linen Throw	28
10	Jigsaw Puzzle 1000	49

A correlated subquery works everywhere. Most databases also have a join-style syntax: UPDATE … FROM (PostgreSQL, SQL Server, SQLite 3.33+) or UPDATE t1 JOIN t2 … (MySQL).

SQL

UPDATE products
SET stock = stock - pending.units
FROM (
  SELECT oi.product_id, SUM(oi.quantity) AS units
  FROM order_items AS oi
  JOIN orders AS o ON o.id = oi.order_id
  WHERE o.status = 'pending'
  GROUP BY oi.product_id
) AS pending
WHERE products.id = pending.product_id;

SELECT id, name, stock FROM products WHERE id IN (2, 9, 10);

Result 3 rows

id	name	stock
2	Learning SQL Step by Step	22
9	Linen Throw	28
10	Jigsaw Puzzle 1000	49

Constraints still apply

SQL

UPDATE products SET price = -5 WHERE id = 1;

Result

Error: CHECK constraint failed: price >= 0

Try it yourself

Mark all of Ana's (customer 1) shipped orders as delivered, then check.

PreviousINSERT NextDELETE