Ever found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE with a subquery comes in handy. Let’s break it down.

Sample Data

First, let’s set up some sample tables:

-- Create and populate the departments table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    budget DECIMAL(10, 2)
);

INSERT INTO departments (dept_id, dept_name, budget)
VALUES 
    (1, 'HR', 500000),
    (2, 'IT', 1000000),
    (3, 'Sales', 750000);

-- Create and populate the employees table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2)
);

INSERT INTO employees (emp_id, name, dept_id, salary)
VALUES 
    (1, 'Homer Simpson', 1, 60000),
    (2, 'Peter Griffin', 2, 75000),
    (3, 'Beatrice Johnson', 2, 65000),
    (4, 'Becky Brown', 3, 80000),
    (5, 'Charlie Davis', 3, 70000);

Now that we’ve got our tables set up, let’s start with a simple example. Say we want to give a 10% raise to all employees in the IT department.

Start with a SELECT Statement

When we use a subquery in the UPDATE statement, oftentimes we can use the same subquery that we’d use if we were simply selecting the rows we want to update.

Therefore, before going right ahead and writing the UPDATE statement, and then immediately running that against the database, it’s usually a good idea to start by selecting the data with a SELECT statement first. That way if we’ve made a mistake in our query logic, we can catch it and correct it before it’s too late.

So let’s go ahead with a SELECT statement:

SELECT * FROM employees WHERE dept_id = 2;

Result:

emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        75000 
3       Beatrice Johnson  2        65000 

OK, so there are two employees in the IT department.

But wait! What if we don’t know the department ID for IT?

Well, that’s where the subquery comes in:

SELECT * FROM employees 
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    WHERE dept_name = 'IT'
    );

Result:

emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        75000 
3       Beatrice Johnson  2        65000 

Same result, as expected. All we needed to know was the department name, and the subquery went to work and dug up the corresponding ID for us. That was passed to the outer query and so the query was able to return the intended results.

So the same concept would apply if we wanted to update the table. We could use a subquery to identify the ID of the IT department.

So to reiterate, the good thing about running a SELECT statement first is that we can see which rows are going to be updated before we run the UPDATE statement. That way, if we’ve made a mistake in our query, we can correct it before updating any rows and making a complete mess of things.

Convert it to an UPDATE Statement

So we can now take our SELECT statement from above and modify the first couple of lines to convert it into an UPDATE statement:

UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    WHERE dept_name = 'IT'
    );

So just to be sure, this subquery finds the ID of the IT department and then the outer UPDATE statement uses that ID in its WHERE clause.

Let’s check the result of the update:

SELECT * FROM employees 
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    WHERE dept_name = 'IT'
    );

Result:

emp_id  name              dept_id  salary
------  ----------------  -------  ------
2       Peter Griffin     2        82500 
3       Beatrice Johnson  2        71500 

Nice to see that they’ve received their well deserved raise!

Another UPDATE with a Subquery

Let’s kick it up a notch. Suppose we want to give a 5% raise to all employees in departments with a budget over 700,000. Here’s how we can do that:

UPDATE employees
SET salary = salary * 1.05
WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 700000);

In this case, our subquery might return multiple department IDs, so we use the IN operator instead of the equals operator (=).

Let’s take a look at the data now:

SELECT 
    e.name, 
    d.dept_name, 
    e.salary 
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

name              dept_name  salary
----------------  ---------  ------
Homer Simpson     HR         60000 
Peter Griffin     IT         86625 
Beatrice Johnson  IT         75075 
Becky Brown       Sales      84000 
Charlie Davis     Sales      73500

So the only person not to receive a raise was Homer Simpson from the HR department.

More Subqueries

Check out my article called Understanding the SQL Subquery that goes over various types of subqueries, along with examples.