UPDATE with JOIN in SQL

To update one table using values from another, every major database supports a join in UPDATE — but the syntax differs: SQL Server uses UPDATE ... FROM ... JOIN, MySQL joins right in the UPDATE clause, and Postgres uses UPDATE ... FROM with the join condition in WHERE.

How Do You UPDATE with a JOIN in SQL Server?

SQL Server takes a FROM clause with a normal JOIN; update the alias, not the table name.

UPDATE o
SET o.status = 'vip'
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.tier = 'gold';

How Do You UPDATE with a JOIN in MySQL?

MySQL puts the join directly after UPDATE — there is no FROM clause.

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.status = 'vip'
WHERE c.tier = 'gold';

How Do You UPDATE with a JOIN in Postgres?

Postgres uses UPDATE ... FROM, with the join condition living in WHERE. Do not repeat the target table in FROM, and don't prefix the SET column with an alias.

UPDATE orders o
SET status = 'vip'          -- no alias on the SET column
FROM customers c
WHERE c.id = o.customer_id
  AND c.tier = 'gold';

What Is the Safe Pattern for UPDATE with JOIN?

Run the join as a SELECT first, check the row count and a sample, then convert it to the UPDATE — inside a transaction so you can roll back.

-- 1. Verify exactly which rows will change:
SELECT o.id, o.status, c.tier
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.tier = 'gold';
-- 137 rows — matches expectation

-- 2. Same join, now as the UPDATE, wrapped in a transaction:
BEGIN;
UPDATE orders o SET status = 'vip'
FROM customers c
WHERE c.id = o.customer_id AND c.tier = 'gold';
-- UPDATE 137  <- must match step 1
COMMIT;   -- or ROLLBACK if the count is wrong

See transactions and ACID for how BEGIN/ROLLBACK protect you here.

Common Pitfalls

• One-to-many joins: if a target row matches several source rows, which value wins is nondeterministic. Deduplicate the source first.
• Postgres alias in SET: SET o.status = ... is a syntax error; write the bare column name.
• Unmatched rows: an inner join leaves non-matching rows untouched — usually what you want, but verify with the SELECT-first step.
• Missing WHERE in MySQL/SQL Server: dropping the filter updates every joined row. The SELECT-first count catches this before it hurts.