What you will be able to do
After this lesson you can correct a value, give a group of people a raise, fill in a missing link between tables, and check each change with a SELECT before and after, so no UPDATE is a surprise.
The basic UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;SET lists the new values; WHERE chooses the rows. The habit that saves careers is: write the SELECT first. Use the same WHERE to see what you are about to change.
Employee 4, Rohan, has the job title Developer. He is being promoted:
SELECT id, first_name, job_title, salary FROM employees WHERE id = 4;+----+------------+-----------+--------+
| id | first_name | job_title | salary |
+----+------------+-----------+--------+
| 4 | Rohan | Developer | 82000 |
+----+------------+-----------+--------+UPDATE employees
SET job_title = 'Senior Developer', salary = 95000
WHERE id = 4;
SELECT id, first_name, job_title, salary FROM employees WHERE id = 4;+----+------------+------------------+--------+
| id | first_name | job_title | salary |
+----+------------+------------------+--------+
| 4 | Rohan | Senior Developer | 95000 |
+----+------------+------------------+--------+Two columns changed in one statement, separated by a comma. Filtering on the primary key (id) guarantees exactly one row is affected.
Calculating from the old value
On the right of = you can use the column's current value. This adds 5,000 to everyone in Support (department 5). Preview, change, and look again:
SELECT first_name, salary FROM employees WHERE department_id = 5 ORDER BY id;+------------+--------+
| first_name | salary |
+------------+--------+
| Pooja | 85000 |
| Karan | 56000 |
| Sana | 56000 |
+------------+--------+UPDATE employees
SET salary = salary + 5000
WHERE department_id = 5;
SELECT first_name, salary FROM employees WHERE department_id = 5 ORDER BY id;+------------+--------+
| first_name | salary |
+------------+--------+
| Pooja | 90000 |
| Karan | 61000 |
| Sana | 61000 |
+------------+--------+Three rows changed. Most tools also report "3 rows affected" after the statement, which is worth checking against your expectation: if you expected three and see 300, stop and investigate.
For a percentage, multiply. Integer arithmetic keeps the example exact: salary * 110 / 100 is a 10 percent raise in whole numbers. (Other databases may give a decimal result, so check your column types.)
SELECT first_name, salary FROM employees WHERE department_id = 3 ORDER BY id;+------------+--------+
| first_name | salary |
+------------+--------+
| Dev | 110000 |
| Anika | 68000 |
| Yash | 64000 |
+------------+--------+UPDATE employees
SET salary = salary * 110 / 100
WHERE department_id = 3 AND job_title <> 'Marketing Manager';
SELECT first_name, job_title, salary FROM employees WHERE department_id = 3 ORDER BY id;+------------+--------------------+--------+
| first_name | job_title | salary |
+------------+--------------------+--------+
| Dev | Marketing Manager | 110000 |
| Anika | Content Strategist | 74800 |
| Yash | Designer | 70400 |
+------------+--------------------+--------+Dev, the Marketing Manager, is excluded by the second condition, so his salary is unchanged.
Using a subquery for the new value
The value after = can be a subquery that returns one value. Employee 18, Ravi, has no department. Assign him to Research without hard-coding the department's id:
SELECT id, first_name, department_id FROM employees WHERE id = 18;+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
| 18 | Ravi | NULL |
+----+------------+---------------+UPDATE employees
SET department_id = (SELECT id FROM departments WHERE name = 'Research')
WHERE id = 18;
SELECT e.id, e.first_name, e.department_id, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.id = 18;+----+------------+---------------+------------+
| id | first_name | department_id | department |
+----+------------+---------------+------------+
| 18 | Ravi | 6 | Research |
+----+------------+---------------+------------+The subquery must return a single value. Look up by a unique column such as the department name, because if it matched two rows the database would raise an error or, in some systems, pick one, which is worse.
Different changes for different rows: CASE
One statement with a CASE can apply tiered raises. Here Engineering employees hired before 2021 get 3,000, and the rest get 1,000:
SELECT first_name, hire_date, salary FROM employees WHERE department_id = 1 ORDER BY id;+------------+------------+--------+
| first_name | hire_date | salary |
+------------+------------+--------+
| Meera | 2018-03-12 | 180000 |
| Arjun | 2019-07-01 | 125000 |
| Kavya | 2020-01-20 | 125000 |
| Rohan | 2021-06-14 | 95000 |
| Isha | 2022-09-05 | 78000 |
| Diya | 2024-06-10 | 60000 |
+------------+------------+--------+UPDATE employees
SET salary = salary + CASE
WHEN hire_date < '2021-01-01' THEN 3000
ELSE 1000
END
WHERE department_id = 1;
SELECT first_name, hire_date, salary FROM employees WHERE department_id = 1 ORDER BY id;+------------+------------+--------+
| first_name | hire_date | salary |
+------------+------------+--------+
| Meera | 2018-03-12 | 183000 |
| Arjun | 2019-07-01 | 128000 |
| Kavya | 2020-01-20 | 128000 |
| Rohan | 2021-06-14 | 96000 |
| Isha | 2022-09-05 | 79000 |
| Diya | 2024-06-10 | 61000 |
+------------+------------+--------+Rohan's salary also includes the 95,000 set earlier in this lesson, so he ends at 96,000.
Updating an entire table
If you leave out WHERE, every row is changed. This statement would set every employee's salary to 1:
UPDATE employees
SET salary = 1;It is valid SQL and databases run it without a warning, which is why it is not executed here. When you really mean "all rows", run it inside a transaction (see the transactions lesson), check the result, and only then commit.
A safe routine for every UPDATE
It is worth turning the lesson into a routine. First write a SELECT with the intended WHERE and read the rows it returns, checking that the count is what you expect. Then run the UPDATE with the identical WHERE, ideally inside a transaction. Finally run the SELECT again and compare. Doing this takes a few seconds, and it is the difference between a small correction and an incident that needs a backup restore. The same routine applies to DELETE, which you meet in the next lesson.
Differences between databases
- Updating from another table. There is no single portable syntax for "UPDATE using a join". MySQL writes
UPDATE a JOIN b ON ... SET ...; PostgreSQL, SQL Server and SQLite (3.33 and newer) writeUPDATE a SET ... FROM b WHERE .... A subquery inSETorWHERE, as above, works in all of them. - Integer versus decimal division.
110 / 100is1with integer division and1.1with decimals; which one you get depends on the column types and the database. See the MySQL UPDATE lesson for MySQL specifics such asLIMITand safe-update mode.
Common mistakes
- Forgetting WHERE. Every row changes. Preview with a
SELECTand prefer filtering by the primary key. - Filtering on something that is not unique.
WHERE first_name = 'Neha'might match several people. Check the number of rows affected. - Using AND between assignments. Write
SET a = 1, b = 2, notSET a = 1 AND b = 2. - Mixing up text and numbers.
WHERE department_id = '5'may work in some databases and fail in others. - Expecting an UPDATE to be reversible. Without a transaction or a backup, the old values are gone.
Exercise
Move employee 9 (Tara) to department 3 and give her the job title Marketing Associate. Show the row before and after.
Show one possible answer
SELECT id, first_name, department_id, job_title FROM employees WHERE id = 9;+----+------------+---------------+-----------------+
| id | first_name | department_id | job_title |
+----+------------+---------------+-----------------+
| 9 | Tara | 2 | Sales Associate |
+----+------------+---------------+-----------------+UPDATE employees
SET department_id = 3, job_title = 'Marketing Associate'
WHERE id = 9;
SELECT id, first_name, department_id, job_title FROM employees WHERE id = 9;+----+------------+---------------+---------------------+
| id | first_name | department_id | job_title |
+----+------------+---------------+---------------------+
| 9 | Tara | 3 | Marketing Associate |
+----+------------+---------------+---------------------+Key points
- UPDATE table SET column = value WHERE condition changes the matching rows; every row is changed if you leave out WHERE.
- Write the same WHERE in a SELECT first to preview exactly which rows an UPDATE will touch.
- The new value can be calculated from the old one, for example salary = salary + 5000, and one UPDATE can set several columns separated by commas.
- A subquery can supply the new value, such as the id of a department looked up by name.
- Use CASE inside SET to apply different changes to different rows in a single statement.