How to Update Multiple Columns in SQL with a Subquery

Posted on by Ian

In SQL, it’s not unusual to see subqueries in SELECT statements, where they can help narrow down the results based on a complex condition.

But the SELECT statement isn’t the only place we can put a subquery.

We can use subqueries in an UPDATE statement, and we can update multiple rows too, if required.

In this article, we look at how to update multiple columns by using a SQL subquery in the UPDATE statement.

Sample Database

First, let’s set up some sample tables to work with:

-- Create customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    total_orders INT DEFAULT 0,
    total_spend DECIMAL(10, 2) DEFAULT 0.00
);

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_total DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insert sample data into customers
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES 
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Smith', '[email protected]'),
(3, 'Bob', 'Johnson', '[email protected]'),
(4, 'Sesh', 'Yap', '[email protected]');

-- Insert sample data into orders
INSERT INTO orders (order_id, customer_id, order_date, order_total)
VALUES
(1, 1, '2024-07-01', 100.50),
(2, 1, '2024-07-15', 75.25),
(3, 2, '2024-07-05', 200.00),
(4, 3, '2024-07-10', 50.75),
(5, 2, '2024-07-20', 150.00);

SELECT * FROM customers;
SELECT * FROM orders;

Here’s what the data looks like so far:

 customer_id | first_name | last_name |      email       | total_orders | total_spend 
-------------+------------+-----------+------------------+--------------+-------------
           1 | John       | Doe       | [email protected] |            0 |        0.00
           2 | Jane       | Smith     | [email protected] |            0 |        0.00
           3 | Bob        | Johnson   | [email protected]  |            0 |        0.00
           4 | Sesh       | Yap       | [email protected] |            0 |        0.00

order_id  customer_id  order_date                order_total
--------  -----------  ------------------------  -----------
1         1            2024-07-01T00:00:00.000Z  100.5      
2         1            2024-07-15T00:00:00.000Z  75.25      
3         2            2024-07-05T00:00:00.000Z  200        
4         3            2024-07-10T00:00:00.000Z  50.75      
5         2            2024-07-20T00:00:00.000Z  150

Now that we’ve got our tables set up, let’s say we want to update the total_orders and total_spend columns in the customers table based on the data in the orders table. There are multiple ways we can do this. Below are examples of how we can achieve this across various RDBMSs.

PostgreSQL

Here’s how we can do it with a subquery in PostgreSQL:

UPDATE customers
SET (total_orders, total_spend) = (
    SELECT COUNT(*), SUM(order_total)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

SELECT * FROM customers
ORDER BY customer_id;

Result:

 customer_id | first_name | last_name |      email       | total_orders | total_spend 
-------------+------------+-----------+------------------+--------------+-------------
           1 | John       | Doe       | [email protected] |            2 |      175.75
           2 | Jane       | Smith     | [email protected] |            2 |      350.00
           3 | Bob        | Johnson   | [email protected]  |            1 |       50.75
           4 | Sesh       | Yap       | [email protected] |            0 |        0.00

This query does the following:

  1. It uses a subquery in the SET clause to calculate the values for both columns at once.
  2. The subquery correlates with the outer query using the customer_id, ensuring each customer gets their own totals.
  3. We’re using the COUNT() and SUM() aggregate functions to calculate the totals.
  4. It only updates customers who have at least one order, leaving the others untouched.

After running this query, each customer’s total_orders and total_spend are updated based on their order history.

SQL Server

Here’s an example that works in SQL Server:

UPDATE c
SET 
    c.total_orders = subquery.order_count,
    c.total_spend = subquery.total_spend
FROM customers c
INNER JOIN (
    SELECT 
        customer_id, 
        COUNT(*) as order_count, 
        SUM(order_total) as total_spend
    FROM orders
    GROUP BY customer_id
) subquery ON c.customer_id = subquery.customer_id;

Here’s what’s happening with this query:

  1. We create a subquery that calculates the order count and total spend for each customer.
  2. We join this subquery with the customers table.
  3. We then update the total_orders and total_spend columns with the calculated values.
  4. In this case, the INNER JOIN ensures that only customers with orders are updated.

MySQL & MariaDB

Here’s one that works in MySQL and MariaDB:

UPDATE customers c
JOIN (
    SELECT 
        customer_id, 
        COUNT(*) as order_count, 
        SUM(order_total) as total_spend
    FROM orders
    GROUP BY customer_id
) subquery ON c.customer_id = subquery.customer_id
SET 
    c.total_orders = subquery.order_count,
    c.total_spend = subquery.total_spend;

This query works similarly to the SQL Server version, but with a few MySQL-specific tweaks:

  1. We use UPDATE followed immediately by the table name and an alias.
  2. We then JOIN to our subquery rather than using FROM and INNER JOIN.
  3. The SET clause comes at the end of the query.

If you want to update only customers who have placed orders, this query already does that implicitly because of the JOIN. However, if you want to make it more explicit, you could add a WHERE clause:

UPDATE customers c
JOIN (
    SELECT 
        customer_id, 
        COUNT(*) as order_count, 
        SUM(order_total) as total_spend
    FROM orders
    GROUP BY customer_id
) subquery ON c.customer_id = subquery.customer_id
SET 
    c.total_orders = subquery.order_count,
    c.total_spend = subquery.total_spend
WHERE subquery.order_count > 0;

More About Subqueries

I wrote an article called Understanding the SQL Subquery that covers various types of subqueries, along with examples.

SQL Joins

Many queries that use subqueries can also be done using joins. See my SQL Joins Tutorial for more about how joins work in SQL.