Logo
Back

How to update a table using JOIN in SQL Server?

Updating one table with values from another table is a common requirement in data manipulation. In SQL Server, you can accomplish this by combining an UPDATE statement with a JOIN. Below are steps and examples showing how to achieve this effectively.

1. Basic Syntax

UPDATE TargetTable
SET    TargetTable.ColumnToUpdate = SourceTable.ColumnWithNewValue
FROM   TargetTable
JOIN   SourceTable
       ON TargetTable.JoinKey = SourceTable.JoinKey
WHERE  <Optional Condition>;
  1. TargetTable: The table whose rows you want to update.
  2. SourceTable: The table (or subquery) containing the new values.
  3. JoinKey: The columns used to match rows across the two tables.
  4. ColumnToUpdate: The specific column in the target table to modify.
  5. ColumnWithNewValue: The column in the source table holding the new values.

2. Practical Example

Suppose you have two tables: Employees (your target) and Departments (your source). You want to update the Employees.DepartmentName based on matching Department IDs:

UPDATE e
SET    e.DepartmentName = d.DepartmentName
FROM   Employees e
JOIN   Departments d
       ON e.DepartmentID = d.DepartmentID
WHERE  e.IsActive = 1;  -- Update only for active employees

In this example:

  • Employees is the target table (aliased as e).
  • Departments is the source table (aliased as d).
  • You only update rows where IsActive is 1.

3. Tips and Considerations

  1. Use Table Aliases: Aliases (e, d) make your query more concise.
  2. WHERE Clause: Always include a WHERE clause if you need to filter specific rows to avoid unintentional bulk updates.
  3. Transactions: For critical updates, consider wrapping your UPDATE in a transaction, so you can roll back if needed.
  4. Locking & Concurrency: Large UPDATE statements can lock tables. Ensure proper indexing and plan your updates during low-traffic times if possible.

4. Strengthen Your SQL Skills with DesignGurus.io

  1. Grokking SQL for Tech Interviews – Master common SQL interview questions, learn about query optimization, and practice hands-on problems.
  2. Grokking Database Fundamentals for Tech Interviews – Build a robust understanding of relational schemas, indexing, and normalization for real-world databases.
  3. For advanced design strategies and high-performance schemas, explore Relational Database Design and Modeling for Software Engineers.

If you want more tips and insights, check out the DesignGurus.io YouTube channel. For personalized feedback on your SQL or system design approach, consider their Mock Interviews.

Conclusion

By combining an UPDATE statement with a JOIN, you can efficiently synchronize data between related tables in SQL Server. Just remember to carefully specify your join condition and include a WHERE clause if you need precise targeting of rows. This technique is particularly handy for maintaining referential integrity, syncing lookup data, or standardizing records across multiple tables.

CONTRIBUTOR
TechGrind