MySQL Update Join: Update One Table Using Another

Sometimes the new value lives in another table. MySQL has a multi-table UPDATE syntax that joins the tables.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    total_spent DECIMAL(10,2) NOT NULL DEFAULT 0,
    tier VARCHAR(10) NOT NULL DEFAULT 'bronze'
);
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(10) NOT NULL
);
INSERT INTO customers (name) VALUES ('Maya'), ('Rohan'), ('Sana'), ('Tara');
INSERT INTO orders (customer_id, total, status) VALUES
    (1, 680.00, 'delivered'), (1, 1299.00, 'delivered'), (2, 145.00, 'shipped'),
    (3, 450.00, 'delivered'), (3, 80.00, 'cancelled'), (3, 999.00, 'delivered');

UPDATE … JOIN

Set each customer’s total_spent from a summary of their delivered orders:

query.sql

SQL

UPDATE customers c
JOIN (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
) s ON s.customer_id = c.id
SET c.total_spent = s.spent;

SELECT id, name, total_spent FROM customers ORDER BY id;

Output

OUTPUT

+----+-------+-------------+
| id | name  | total_spent |
+----+-------+-------------+
|  1 | Maya  |     1979.00 |
|  2 | Rohan |        0.00 |
|  3 | Sana  |     1449.00 |
|  4 | Tara  |        0.00 |
+----+-------+-------------+
4 rows in set

The JOIN pairs each customer with their summary row, and SET assigns from it. Customers without a matching summary (Tara and Rohan, who have no delivered orders) are not changed, because an INNER JOIN drops them.

Updating the unmatched rows too: LEFT JOIN

query.sql

SQL

UPDATE customers c
LEFT JOIN (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
) s ON s.customer_id = c.id
SET c.total_spent = COALESCE(s.spent, 0);

SELECT id, name, total_spent FROM customers ORDER BY id;

Output

OUTPUT

+----+-------+-------------+
| id | name  | total_spent |
+----+-------+-------------+
|  1 | Maya  |     1979.00 |
|  2 | Rohan |        0.00 |
|  3 | Sana  |     1449.00 |
|  4 | Tara  |        0.00 |
+----+-------+-------------+
4 rows in set

LEFT JOIN keeps every customer, and COALESCE turns the missing total into 0.

UPDATE with a correlated subquery (standard SQL)

The standard way to look up a value from another table is a subquery in the SET. It also works on other databases:

query.sql

SQL

UPDATE customers
SET tier = (
    SELECT CASE WHEN COALESCE(SUM(o.total), 0) >= 2000 THEN 'gold'
                WHEN COALESCE(SUM(o.total), 0) >= 500  THEN 'silver'
                ELSE 'bronze' END
    FROM orders o
    WHERE o.customer_id = customers.id AND o.status = 'delivered'
);
SELECT name, total_spent, tier FROM customers ORDER BY id;

Output

OUTPUT

+-------+-------------+--------+
| name  | total_spent | tier   |
+-------+-------------+--------+
| Maya  |     1979.00 | silver |
| Rohan |        0.00 | bronze |
| Sana  |     1449.00 | silver |
| Tara  |        0.00 | bronze |
+-------+-------------+--------+
4 rows in set

For each customer, the subquery calculates the tier from their own orders. The subquery returns exactly one value per customer (an aggregate always returns one row). If a subquery returned more than one row, you would get error 1242, “Subquery returns more than 1 row”.

You cannot select from the table you update

query.sql

SQL

1
2
3

UPDATE orders
SET total = total + 10
WHERE id IN (SELECT id FROM orders WHERE status = 'shipped');

Output

OUTPUT

ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause

Error 1093: MySQL does not allow a subquery on the table that is being changed (in the same statement). The usual fix is to wrap the subquery in a derived table, which MySQL materialises first:

query.sql

SQL

UPDATE orders
SET total = total + 10
WHERE id IN (SELECT id FROM (SELECT id FROM orders WHERE status = 'shipped') AS shipped_ids);
SELECT id, status, total FROM orders WHERE status = 'shipped';

Output

OUTPUT

+----+---------+--------+
| id | status  | total  |
+----+---------+--------+
|  3 | shipped | 155.00 |
+----+---------+--------+
1 row in set

Often a plain WHERE is simpler (WHERE status = 'shipped'). The workaround is needed when the condition truly depends on other rows of the same table.

Updating two tables in one statement

MySQL can update columns of several tables in one statement. It is rarely a good idea, because it is MySQL-specific and hard to read, but it exists:

query.sql

SQL

UPDATE customers c JOIN orders o ON o.customer_id = c.id
SET c.tier = 'vip', o.status = 'priority'
WHERE c.name = 'Rohan';
SELECT c.name, c.tier, o.status FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.name = 'Rohan';

Output

OUTPUT

+-------+------+----------+
| name  | tier | status   |
+-------+------+----------+
| Rohan | vip  | priority |
+-------+------+----------+
1 row in set

Notes

ORDER BY and LIMIT are not allowed in a multi-table UPDATE.
Check that the join produces one row per target row. If the joined table has several matches, MySQL updates the row from one of them, and you do not know which.
Index the join columns, or the update will be slow.
Preview with a SELECT that has the same joins and conditions.

info

MySQL syntax

UPDATE ... JOIN ... SET is MySQL syntax. PostgreSQL and SQL Server use UPDATE ... FROM .... Subqueries in SET are portable.