DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to update with correlated subquery?

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?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE