How to UPDATE from a SELECT in SQL

Posted by Kyle Hankinson August 22, 2021

---

Updating rows in one table based on values from another table is a common operation, but the syntax is different in every database.

The Scenario

You have a products table and a price_updates table. You want to apply the new prices:

-- products                    -- price_updates
-- id | name    | price        -- product_id | new_price
-- 1  | Widget  | 10.00        -- 1          | 12.00
-- 2  | Gadget  | 25.00        -- 2          | 23.00
-- 3  | Gizmo   | 15.00

SQL Server

SQL Server uses UPDATE ... FROM ... JOIN:

UPDATE p
SET p.price = pu.new_price
FROM products p
INNER JOIN price_updates pu ON p.id = pu.product_id;

The alias p in the UPDATE clause refers to the table in the FROM clause.

MySQL

MySQL puts the JOIN directly after UPDATE:

UPDATE products p
INNER JOIN price_updates pu ON p.id = pu.product_id
SET p.price = pu.new_price;

Note the order: UPDATE ... JOIN ... SET (not UPDATE ... SET ... FROM).

PostgreSQL

PostgreSQL uses UPDATE ... SET ... FROM ... WHERE:

UPDATE products
SET price = pu.new_price
FROM price_updates pu
WHERE products.id = pu.product_id;

The join condition goes in WHERE, not in a JOIN clause. Do not repeat the target table in the FROM clause.

Oracle

Oracle uses a subquery or the MERGE statement:

Subquery approach:

UPDATE products p
SET price = (
    SELECT pu.new_price
    FROM price_updates pu
    WHERE pu.product_id = p.id
)
WHERE EXISTS (
    SELECT 1 FROM price_updates pu
    WHERE pu.product_id = p.id
);

The WHERE EXISTS prevents setting price to NULL for products without an update.

MERGE (preferred):

MERGE INTO products p
USING price_updates pu ON (p.id = pu.product_id)
WHEN MATCHED THEN
    UPDATE SET p.price = pu.new_price;

MERGE is cleaner and also available in SQL Server 2008+.

Quick Reference

Database	Syntax
SQL Server	UPDATE alias SET ... FROM table JOIN ...
MySQL	UPDATE table JOIN ... SET ...
PostgreSQL	UPDATE table SET ... FROM ... WHERE ...
Oracle	MERGE INTO ... USING ... WHEN MATCHED THEN UPDATE SET ...

Updating Multiple Columns

The pattern works the same way — just add more columns to the SET clause:

-- MySQL example
UPDATE products p
INNER JOIN price_updates pu ON p.id = pu.product_id
SET p.price = pu.new_price,
    p.updated_at = NOW();

Safety Tip

Always run a SELECT with the same join first to preview which rows will be affected:

SELECT p.id, p.name, p.price AS old_price, pu.new_price
FROM products p
INNER JOIN price_updates pu ON p.id = pu.product_id;

---