How can I do an UPDATE statement with JOIN in SQL Server?
Want to update data in one table using related data from another? Learn how SQL Server allows you to use the UPDATE statement combined with JOIN to modify rows based on matching values in a related table. This is especially useful for syncing or correcting records across multiple tables.
Updating one table using data from another in SQL Server is totally doable using a combination of UPDATE and JOIN. This is super handy when you want to sync or transfer related values between two tables based on a common column.
Here’s the basic syntax to do it:
UPDATE t1
SET t1.column_name = t2.column_name
FROM Table1 t1
JOIN Table2 t2 ON t1.common_field = t2.common_field
WHERE some_condition;Let’s break it down with an example:
Suppose you have two tables:
- Employees (with columns: EmployeeID, DepartmentID)
- Departments (with columns: DepartmentID, DepartmentName)
You want to update the Employees table and set a new department name based on matches in the Departments table.
UPDATE e
SET e.DepartmentID = d.DepartmentID
FROM Employees e
JOIN Departments d ON e.DepartmentName = d.DepartmentName
WHERE e.DepartmentID IS NULL;Key Points:
- Always use aliases (e, d) to keep things clean.
- Ensure the JOIN condition is correct to avoid updating unintended rows.
- The WHERE clause is optional but helps limit updates.
Always test your query with a SELECT first to see what will be affected. And if possible, try it on a staging environment before running it in production!