SQL Tutorial · Changing Data and Tables · Lesson 28 of 36

SQL UPDATE

Change existing rows — and always use WHERE.

UPDATE ... SET ... WHERE

Example 1Try it yourself

UPDATE products SET stock = stock + 20 WHERE stock = 0;
SELECT name, stock FROM products WHERE product_id IN (3, 10);

Output

name | stock
The Pragmatic Programmer | 20
Standing Desk Mat | 20

Always include a WHERE clause. UPDATE products SET price = 0 changes every row. A good habit: write the WHERE first and test it with a SELECT.

Updating with calculations and subqueries

Example 2Try it yourself

-- 10% raise for everyone in Engineering below the department average
UPDATE employees
SET salary = ROUND(salary * 1.10)
WHERE department = 'Engineering'
  AND salary < (SELECT AVG(salary) FROM employees WHERE department = 'Engineering');
SELECT name, salary FROM employees WHERE department = 'Engineering' ORDER BY salary;

Output

name | salary
Lucas Martin | 123200
Priya Nair | 126500
Jonas Berg | 154000
Mei Chen | 165000
Omar Haddad | 210000

Test yourself

What happens if you run UPDATE without WHERE?

Nothing
Only the first row is updated
Every row in the table is updated
An error is raised

Show answer

Answer: Every row in the table is updated