SQL update with correlated subquery

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. The correlated subquery will be run once for each candidate row selected by the outer query.

Perform an update with a correlated subquery

USE AdventureWorks;

UPDATE d 
SET Name =
(
 SELECT Name FROM
 BackupOfAdventureWorks.HumanResources.Department 
 WHERE DepartmentID = d.DepartmentID
)
FROM HumanResources.Department d;

Perform an update using JOIN

USE AdventureWorks;

UPDATE d 
SET d.Name = bd.Name 
FROM HumanResources.Department d
JOIN BackupOfAdventureWorks.HumanResources.Department bd
ON bd.DepartmentID = d.DepartmentID;