SQL UPDATE JOIN – Update One Table Using Data from Another
💡 Concept
UPDATE with JOIN is a powerful SQL technique that lets you refresh or modify rows in one table using values from another related table. It’s especially useful when you need to sync data across multiple tables.
📘 Quick Intro
Imagine you have two tables: one with employee records and another with updated salary information. Instead of manually updating each row, SQL allows you to join these tables and update matching records in a single query. That’s where UPDATE ... JOIN (or UPDATE ... FROM depending on the database) comes into play.
🧠 Analogy
Think of it like updating your phone contacts. You have your old phonebook, and your friend sends you a newer version. Instead of rewriting the entire list, you compare names, and only the changed numbers get updated. SQL’s UPDATE JOIN works the same way: it matches rows and applies updates only where needed.
🔧 Technical Explanation
- SQL Server / PostgreSQL: Use
UPDATE ... FROMwith a join to update rows. - MySQL: Uses
UPDATE ... JOINsyntax directly. - Join Condition: A key (like
EmployeeID) is used to match rows between tables. - Selective Updates: Only matching rows are updated—no accidental overwrite of unrelated data.
- Aliases: Short table names (A, B) improve readability and reduce mistakes.
💻 Example Queries
-- SQL Server / PostgreSQL Syntax
UPDATE A
SET A.Salary = B.NewSalary
FROM Employees A
JOIN SalaryUpdates B ON A.EmployeeID = B.EmployeeID;
-- MySQL Syntax
UPDATE Employees A
JOIN SalaryUpdates B ON A.EmployeeID = B.EmployeeID
SET A.Salary = B.NewSalary;
👉 Always test your join condition with a SELECT query first to preview results before applying an UPDATE.
❓ Interview Q&A
Q1: Can you update one table using another?
A: Yes, using UPDATE with JOIN or FROM.
Q2: Which databases support UPDATE ... FROM?
A: SQL Server and PostgreSQL.
Q3: How does MySQL differ?
A: MySQL uses UPDATE ... JOIN instead of UPDATE ... FROM.
Q4: Why is the join condition important?
A: It ensures only the intended rows are updated.
Q5: What happens if the condition is wrong?
A: You may accidentally update too many or too few rows.
Q6: Can you filter updates?
A: Yes, by adding a WHERE clause.
Q7: Can multiple columns be updated?
A: Yes, list them in the SET clause.
Q8: Is there an alternative to joins?
A: Correlated subqueries can be used.
Q9: How can you test safely?
A: Run a SELECT before the UPDATE.
Q10: Should indexes be considered?
A: Yes, indexing join keys improves performance.
📝 MCQs
Q1. Which SQL clause updates rows from another table?
- SELECT INTO
- UPDATE ... JOIN or UPDATE ... FROM
- MERGE
- INSERT INTO
Q2. Which RDBMS supports UPDATE ... FROM?
- MySQL only
- SQL Server
- SQLite
- Oracle
Q3. What must you include to match rows?
- WHERE 1=1
- Group By
- JOIN condition
- Alias
Q4. How does MySQL handle update joins?
- UPDATE FROM
- MERGE only
- UPDATE with JOIN directly
- CTE required
Q5. Why run SELECT before UPDATE?
- It improves speed
- To preview affected rows
- To create backup
- To drop triggers
Q6. What happens if WHERE is omitted?
- No update
- Partial update
- All matched rows will be updated
- Only top 1 row
Q7. Which tool previews query results?
- EXEC
- DROP
- SELECT with JOIN
- ROLLBACK
Q8. Can multiple columns be updated?
- No
- Only one
- Yes
- Only in PostgreSQL
Q9. Is indexing important?
- No
- Yes, for performance
- Only in SELECT
- Only in MySQL
Q10. Which command undoes an update?
- SAVE
- RETRY
- ROLLBACK
- RESTORE
💡 Bonus Insight
When updating production data, use transactions so you can roll back if something goes wrong. Also, test your update with a SELECT first. For complex updates, Common Table Expressions (CTEs) make queries more readable and safer to debug.
📄 Download PDF
Want a quick reference? Download this article as a PDF and keep it in your study notes.