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;