How can I do an UPDATE statement with JOIN in SQL Server?

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!