SQL UPDATE Statement

UPDATE changes values in rows that already exist: give a raise, fix a typo, mark an order as shipped. It is simple to write and easy to get dangerously wrong, because without a WHERE clause it changes every row. This lesson shows the safe way to do it, step by step.

UPDATE syntax

UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;

Sample data

Update one row

Karan has been promoted:

UPDATE employeesSET job_title = 'Senior Developer'WHERE emp_id = 104; SELECT emp_id, first_name, job_title FROM employees WHERE emp_id = 104;

Update several columns

Separate the assignments with commas:

UPDATE employeesSET city = 'Hyderabad', salary = 80000WHERE emp_id = 103; SELECT emp_id, first_name, city, salary FROM employees WHERE emp_id = 103;

Calculated updates

The new value can use the old one. A 10% raise for the Marketing department:

UPDATE employeesSET salary = salary * 1.10WHERE dept_id = 3; SELECT first_name, salary FROM employees WHERE dept_id = 3;

Update with CASE

Different raises for different job levels, in one statement:

UPDATE employeesSET salary = CASE               WHEN salary < 50000 THEN salary + 5000               WHEN salary < 100000 THEN salary + 3000               ELSE salary             ENDWHERE dept_id = 2; SELECT first_name, salary FROM employees WHERE dept_id = 2;

UPDATE with JOIN

Update one table using values from another. Move everyone in the Sales department to the department’s own location:

UPDATE employees eJOIN departments d ON d.dept_id = e.dept_idSET e.city = d.locationWHERE d.dept_name = 'Sales'; SELECT e.first_name, e.city, d.locationFROM employees e JOIN departments d ON d.dept_id = e.dept_idWHERE d.dept_name = 'Sales';

Update with a subquery

The WHERE clause can use a subquery. Put every product that has never been ordered on sale, 300 off:

UPDATE productsSET price = price - 300WHERE product_id NOT IN (SELECT product_id FROM order_items); SELECT product_id, product_name, price FROM products WHERE product_id = 9;

Safe UPDATE habits

1. Run the WHERE as a SELECT first. SELECT * FROM employees WHERE dept_id = 3; shows exactly which rows will change.
2. Use a transaction for important changes, so you can ROLLBACK a mistake (Transactions).
3. Turn on safe updates in interactive sessions: SET SQL_SAFE_UPDATES = 1; makes MySQL refuse UPDATE and DELETE without a key-based WHERE or LIMIT. MySQL Workbench enables it by default.

SET SQL_SAFE_UPDATES = 1;UPDATE employees SET salary = 0;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

UPDATE productsSET stock = 75WHERE product_id = 3; SELECT product_id, product_name, stock FROM products WHERE product_id = 3;

UPDATE productsSET price = price + 20WHERE category = 'Stationery'; SELECT product_name, price FROM products WHERE category = 'Stationery';

UPDATE FAQ