How Can You Update Records from One Table Based on Another Table?
💡 Concept Name
UPDATE with JOIN in SQL – A method to update a table’s data using corresponding values from another related table.
📘 Quick Intro
When working with relational databases, it’s common to update rows in one table using data from another. SQL provides syntax that combines UPDATE with JOIN to do this efficiently.
🧠 Analogy / Short Story
Imagine you're updating your contact list based on a more recent list your friend has. You look at matching names and update only the phone numbers that have changed. SQL behaves similarly: it matches rows using a join condition and updates only where needed.
🔧 Technical Explanation
- In SQL Server and PostgreSQL,
UPDATE ... FROMis used to update values using a join. - MySQL supports
UPDATE ... JOINsyntax directly. - A common join condition (e.g., primary key or foreign key) is used to link both tables.
- Only the matched records are updated, avoiding full-table overwrites.
- Aliases are often used for readability in the update statement.
💻 Real Code Example
-- 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;
❓ Interview Q&A
Q1: Can you update one table using another table’s data?
A: Yes, using an UPDATE with JOIN or FROM clause depending on the SQL dialect.
Q2: What SQL dialects support UPDATE ... FROM?
A: SQL Server and PostgreSQL support UPDATE ... FROM syntax.
Q3: How is MySQL’s syntax different?
A: MySQL uses UPDATE ... JOIN to perform updates across tables.
Q4: What’s the role of the join condition?
A: It links matching rows from the source and target tables to apply correct updates.
Q5: What happens if the join condition is incorrect?
A: You may update unintended rows or fail to update matching records — always verify join logic.
Q6: Can you use WHERE clause with update joins?
A: Yes, to filter which rows to update further.
Q7: Is it possible to update multiple columns from another table?
A: Yes, you can update several fields in the SET clause using the joined table’s data.
Q8: Are subqueries an alternative to join updates?
A: Yes, correlated subqueries in the SET clause can also fetch update values.
Q9: How can you test your update logic safely?
A: Run a SELECT with the same join first to preview which rows will be updated.
Q10: Should indexes be considered in such updates?
A: Yes, ensure join keys are indexed to optimize performance and prevent slow updates.
📝 MCQs
Q1. Which SQL clause is used to update based on 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 in both tables?
- 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 is testing SELECT before UPDATE helpful?
- It improves speed
- To preview affected rows
- To create backup
- To drop triggers
Q6. What happens if you omit a WHERE clause in update?
- No update
- Partial update
- All matched rows will be updated
- Only top 1 row
Q7. What tool can help preview query results before applying update?
- EXEC
- DROP
- SELECT with JOIN
- ROLLBACK
Q8. Can you update multiple columns in one query?
- No
- Only one
- Yes
- Only in PostgreSQL
Q9. Is indexing the join column important?
- No
- Yes, for performance
- Only in SELECT
- Only in MySQL
Q10. Which SQL command lets you undo a faulty update?
- SAVE
- RETRY
- ROLLBACK
- RESTORE
💡 Bonus Insight
When dealing with production databases, always test your join update with a SELECT query first. Backups or transactions help mitigate mistakes. Some systems also support CTE-based updates for better readability.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!