Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to perform updates with a correlated subquery or a JOIN respectively:

 
USE AdventureWorks;
-- SQL update with correlated subquery - UPDATE with SELECT subquery
UPDATE d
SET    Name = (SELECT Name
               FROM   BackupOfAdventureWorks.HumanResources.Department
               WHERE  DepartmentID = d.DepartmentID)
FROM   HumanResources.Department d;
 
GO
 
-- SQL update with join - SQL Server update with inner join
UPDATE d
SET    d.Name = bd.Name
FROM   HumanResources.Department d
       JOIN BackupOfAdventureWorks.HumanResources.Department bd
         ON bd.DepartmentID = d.DepartmentID;
 

GO

SQL subquery definition: A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. When a subquery has a reference to a table in the outer query, it is called correlated subquery.

-- SQL subquery

SELECT soh.SalesOrderID, OrderDate=convert(varchar, soh.OrderDate, 111),

    (SELECT MAX(sod.UnitPrice)

     FROM AdventureWorks.Sales.SalesOrderDetail AS sod) AS MaxUnitPrice

FROM AdventureWorks.Sales.SalesOrderHeader AS soh

ORDER BY OrderDate

GO

/* Partial results

SalesOrderID      OrderDate   MaxUnitPrice

43697             2001/07/01  3578.27

43698             2001/07/01  3578.27

43699             2001/07/01  3578.27

*/

-- SQL correlated subquery – reference to soh.SalesOrderID

SELECT soh.SalesOrderID, OrderDate=convert(varchar, soh.OrderDate, 111),

    (SELECT MAX(sod.UnitPrice)

     FROM AdventureWorks.Sales.SalesOrderDetail AS sod

     WHERE soh.SalesOrderID = sod.SalesOrderID) AS MaxUnitPrice

FROM AdventureWorks.Sales.SalesOrderHeader AS soh

ORDER BY OrderDate

GO

/* Partial results

SalesOrderID      OrderDate   MaxUnitPrice

43659             2001/07/01  2039.994

43660             2001/07/01  874.794

43661             2001/07/01  2039.994

*/
------------
 
-- UPDATE example with correlated subqueries and GROUP BY
UPDATE AdventureWorks2008.Sales.SalesPerson
SET SalesYTD = SalesYTD +
    (SELECT SUM(soh1.SubTotal)
     FROM AdventureWorks2008.Sales.SalesOrderHeader AS soh1
     WHERE soh1.OrderDate = (SELECT MAX(OrderDate)
                             FROM AdventureWorks2008.Sales.SalesOrderHeader AS soh2
                             WHERE soh2.SalesPersonID = soh1.SalesPersonID)
     AND Sales.SalesPerson.BusinessEntityID = soh1.SalesPersonID
     GROUP BY soh1.SalesPersonID);
-- (17 row(s) affected)

------------
 

Related articles:

http://sqlusa.com/articles2005/updatesyntax/

UPDATE (Transact-SQL)

SQL Question: Update using correlated subquery?