UPDATE with Subquery

A subquery inside an UPDATE statement lets you compute new values or filter rows dynamically based on other data in the database. This is useful when the logic is too complex for a simple JOIN or when you need to reference aggregate results.

Subquery in SET Clause

You can use a scalar subquery in the SET clause to compute the new value for a column. The subquery must return exactly one row and one column. If it returns no rows, NULL is assigned.

Subquery in WHERE Clause

A subquery in the WHERE clause filters which rows to update. For example, you can update all products whose category ID matches a list returned by another SELECT statement.

Use EXISTS or IN for correlated subqueries in WHERE
Scalar subqueries in SET must return a single value
MySQL does not allow you to SELECT from the same table you are updating directly — use a derived table workaround

Example

-- Set each product's price to the average price of its category
UPDATE products p
SET p.price = (
    SELECT AVG(p2.price)
    FROM (SELECT * FROM products) AS p2
    WHERE p2.category_id = p.category_id
)
WHERE p.is_active = 1;

SQL

Full Editor

Pro Tip

Wrap the subquery in a derived table (SELECT * FROM table) to avoid "can't reopen table" errors in MySQL.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned