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. When you use a correlated subquery in an UPDATE statement, the correlation name refers to the rows you are interested in updating.

The following examples use AdventureWorks sample database.

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;

How to download, save, attach, and configure the AdventureWorks database.