SQL UPDATE: Change Existing Rows Safely

UPDATE changes values in rows that already exist: a customer moves city, a price changes, an order ships. It is simple to write and easy to get dangerously wrong, so this lesson also covers how to update safely.

The example table#

Syntax#

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

• SET says which columns get which new values.
• WHERE says which rows change. Every row that matches is updated.

Update one row#

Use the primary key to target exactly one row:

UPDATE employees
SET salary = 55000
WHERE id = 1;

SELECT id, name, salary FROM employees WHERE id = 1;

id | name | salary
---+------+-------
1  | Asha | 55000

Update several columns#

Separate assignments with commas. John moves to Hyderabad and joins Engineering:

UPDATE employees
SET city = 'Hyderabad',
    department = 'Engineering'
WHERE name = 'John';

name | department  | city
-----+-------------+----------
John | Engineering | Hyderabad

Calculate the new value from the old one#

The right side of SET can use the column's current value. A 10% raise for Engineering:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

SELECT name, department, salary FROM employees;

name  | department  | salary
------+-------------+-------
Asha  | Sales       | 52000
Ravi  | Engineering | 85800
Meera | Engineering | 89100
John  | Marketing   | 46000
Priya | Sales       | 61000

Both engineers were updated because both matched the WHERE condition. For each row, salary on the right means the value before the update.

The most dangerous mistake in SQL#

An UPDATE without WHERE changes every row in the table:

UPDATE employees
SET salary = 0;

name  | salary
------+-------
Asha  | 0
Ravi  | 0
Meera | 0
John  | 0
Priya | 0

There is no undo button. Unless you were inside a transaction or have a backup, the old salaries are gone.

Updating with values from another table#

You can update rows based on data elsewhere, including other rows of the same table. A subquery is the most portable way:

-- Give a 5% raise to everyone who manages someone
UPDATE employees
SET salary = salary * 1.05
WHERE id IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

This works in PostgreSQL, SQL Server, Oracle and SQLite. MySQL refuses a subquery that reads the same table you are updating (error 1093); there, use UPDATE employees e JOIN (...) m ON ... SET ... instead. PostgreSQL also supports UPDATE ... FROM other_table, and SQL Server supports UPDATE ... FROM ... JOIN.

Common mistakes#

Practice#

UPDATE products
SET stock = stock + 20
WHERE stock = 0;

SELECT name, stock
FROM products
WHERE name = 'Monitor';

name    | stock
--------+------
Monitor | 20

Key takeaway#