How Do You UPDATE with a JOIN in SQL?
Learn how to UPDATE with a JOIN in SQL across MySQL, PostgreSQL and SQL Server, with correct syntax, safety tips and common mistakes to avoid.
Expected Interview Answer
You UPDATE with a JOIN by matching the target table to another table on a key and setting columns from the joined table's values; the exact syntax differs by engine — MySQL joins directly in the UPDATE, while PostgreSQL and SQL Server use UPDATE ... FROM / an accompanying join clause.
The goal is to modify rows in one table using data looked up from another, for example writing each employee's department bonus into the employees table. In MySQL you write UPDATE a JOIN b ON ... SET a.col = b.col; in PostgreSQL it is UPDATE a SET col = b.col FROM b WHERE a.key = b.key; in SQL Server it is UPDATE a SET col = b.col FROM a JOIN b ON .... Always scope the join with a correct condition or you risk updating far more rows than intended, and prefer a matching SELECT first to preview affected rows.
- Bulk-updates rows from related data in one statement
- Avoids slow row-by-row cursors or application loops
- Keeps derived columns in sync with a source table
- Filterable with WHERE for targeted updates
- Runs inside a transaction so it can be rolled back
AI Mentor Explanation
UPDATE with a JOIN is like a scorer copying each player's official registered team into the match sheet by matching player IDs against the central roster. Rather than editing every line by hand, they line the two sheets up on the ID column and stamp the correct team onto each matching entry in one sweep.
Step-by-Step Explanation
Step 1
Identify target and source
Decide which table's rows you are changing (target) and which table supplies the new values (source).
Step 2
Choose the join key
Find the column(s) that correctly relate the two tables, usually a primary/foreign key pair.
Step 3
Use the engine's syntax
MySQL joins in UPDATE directly; PostgreSQL uses UPDATE ... SET ... FROM ... WHERE; SQL Server uses UPDATE ... SET ... FROM ... JOIN.
Step 4
Add a WHERE filter
Restrict the update to the intended rows and ensure the join condition is present so unmatched rows are not touched.
Step 5
Preview then run in a transaction
Run an equivalent SELECT first, then wrap the UPDATE in a transaction so you can ROLLBACK if the row count is wrong.
What Interviewer Expects
- Awareness that syntax differs across MySQL, PostgreSQL, SQL Server
- Correct join condition to avoid updating too many rows
- Use of a WHERE clause to scope the update
- Suggestion to preview with a SELECT first
- Understanding of running inside a transaction for safety
Common Mistakes
- Omitting the join/WHERE condition and updating every row
- Using MySQL's UPDATE JOIN syntax on PostgreSQL
- Joining on the wrong key and corrupting data
- Not previewing affected rows before executing
- Forgetting a transaction, making mistakes hard to undo
Best Answer (HR Friendly)
“You update one table using values looked up from another by matching them on a shared key, like an ID. The exact wording depends on the database, so you match the rows, set the new values, and it is safest to preview the change and run it inside a transaction.”
Code Example
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.bonus = d.bonus_pool
WHERE d.name = 'Sales';UPDATE employees e
SET bonus = d.bonus_pool
FROM departments d
WHERE e.dept_id = d.id
AND d.name = 'Sales';UPDATE e
SET e.bonus = d.bonus_pool
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Sales';Follow-up Questions
- How does UPDATE ... FROM in PostgreSQL differ from MySQL's syntax?
- How do you preview which rows an UPDATE JOIN will affect?
- What happens if the join matches multiple source rows per target row?
- How do you safely test an UPDATE before committing it?
- Can you use a CTE or subquery instead of a JOIN to update?
MCQ Practice
1. Which database uses 'UPDATE t SET col = s.col FROM source s WHERE t.id = s.id'?
PostgreSQL (and SQL Server) support the UPDATE ... FROM form; MySQL joins inside the UPDATE clause instead.
2. What is the biggest risk of an UPDATE with a JOIN?
Without a proper join/WHERE condition the statement can update far more rows than intended, so scope it carefully.
3. What is a safe practice before running an UPDATE JOIN in production?
Previewing the affected rows with a SELECT and wrapping the update in a transaction lets you verify and roll back if needed.
Flash Cards
MySQL UPDATE JOIN syntax? — UPDATE a JOIN b ON a.key = b.key SET a.col = b.col WHERE ...
PostgreSQL equivalent? — UPDATE a SET col = b.col FROM b WHERE a.key = b.key.
SQL Server equivalent? — UPDATE a SET a.col = b.col FROM a JOIN b ON a.key = b.key.
Main danger of UPDATE with JOIN? — Missing or wrong join condition updates unintended rows across the whole table.
Safest way to run it? — Preview with a SELECT, then run inside a transaction so you can ROLLBACK.