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 a GROUP BY?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate how to perform a table UPDATE with the results of a GROUP BY query.

-- SQL GROUP BY Statement Quick Syntax - SQL group by count - group by clause

SELECT Color            = COALESCE(Color,'N/A'), -- grouping column

       ColorCount       = COUNT(* ),             -- aggregate function

       AvgListPrice     = AVG(ListPrice)         -- aggregate function

FROM   AdventureWorks2008.Production.Product

GROUP BY Color

ORDER BY Color

/*    Color       ColorCount  AvgListPrice

Black       93          725.121

Blue        26          923.6792

Grey        1           125.00

………

*/

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

 

-- SQL Update Inner Join Quick Syntax - Update using two tables

UPDATE pod  SET pod.ModifiedDate = poh.ModifiedDate

FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader poh

  INNER JOIN AdventureWorks2008.Purchasing.PurchaseOrderDetail pod

    ON poh.PurchaseOrderID = pod.PurchaseOrderID

WHERE poh.PurchaseOrderID >= 4000

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

 

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

-- TSQL UPDATE table with GROUP BY Quick Syntax

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

-- SQL GROUP BY in Update Statement - update using group by & aggregate function

-- SQL select into table create for demo - T-SQL update query with GROUP BY

SELECT DISTINCT YEAR=YEAR(OrderDate), Sales=CONVERT(money,0.0)

INTO tempdb.dbo.RevenueByYear

FROM AdventureWorks2008.Sales.SalesOrderHeader

GO

UPDATE rby SET rby.Sales = g.Total  -- update using table aliases in FROM clause

FROM tempdb.dbo.RevenueByYear rby   -- SQL UPDATE INNER JOIN

INNER JOIN ( SELECT YEAR=YEAR(OrderDate), Total=SUM(TotalDue) -- SUM aggregate

             FROM AdventureWorks2008.Sales.SalesOrderHeader

             GROUP BY YEAR(OrderDate) ) g   -- GROUP BY on year aliased as "g"

ON rby.Year = g.YEAR

GO

SELECT *, SalesCurrency='$'+CONVERT(varchar,Sales,1) FROM tempdb.dbo.RevenueByYear

ORDER BY YEAR

/*

YEAR  Sales             SalesCurrency

2001  14327552.2263     $14,327,552.23

2002  39875505.095      $39,875,505.10

2003  54307615.0868     $54,307,615.09

2004  32196912.4165     $32,196,912.42

*/

DROP TABLE tempdb.dbo.RevenueByYear

GO

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

USE tempdb;

SELECT Color=ISNULL(Color,'N/A'), ItemCount=0 INTO ProductColor

FROM AdventureWorks2008.Production.Product

GROUP BY Color

GO

-- TSQL update from group by - SQL UPDATE JOIN- SQL update table from group by syntax

UPDATE pc

SET pc.ItemCount = cg.ProductColorCount

FROM ProductColor pc

INNER JOIN (SELECT Color=ISNULL(Color,'N/A'), ProductColorCount=COUNT(*)

            FROM AdventureWorks2008.Production.Product

            GROUP BY Color) cg

ON pc.Color = cg.Color

GO

SELECT * FROM ProductColor

GO

/*

Color             ItemCount

N/A               248

Black             93

Blue              26

Grey              1

Multi             8

Red               38

Silver            43

Silver/Black      7

White             4

Yellow            36

*/

DROP TABLE tempdb.dbo.ProductColor

GO

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

-- T-SQL group by syntax - group by sum aggregate - group by crosstab query

SELECT YEAR = YEAR(OrderDate),

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ,OrderDate) = 1 THEN TotalDue

                                    END),1),'') AS 'Quarter 1',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 2 THEN TotalDue

                                    END),1),'') AS 'Quarter 2',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 3 THEN TotalDue

                                    END),1),'') AS 'Quarter 3',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 4 THEN TotalDue

                                    END),1),'') AS 'Quarter 4'

FROM  AdventureWorks2008.Sales.SalesOrderHeader soh

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

/* Query results

 

YEAR  Quarter 1         Quarter 2         Quarter 3         Quarter 4

2001                                      5,850,932.95      8,476,619.28

2002  7,379,686.31      8,210,285.17      13,458,206.13     10,827,327.49

2003  8,550,831.87      10,749,269.37     18,220,131.53     16,787,382.31

2004  14,170,982.55     17,969,750.95     56,178.92  

*/

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

In the following section, four UPDATE with GROUP BY methods are presented:

derived table, correlated subquery, CTE and subselect.

USE AdventureWorks

GO

 

-- FIRST SOLUTION: update with group by using a DERIVED TABLE- update from a Group By Clause

 

 -- Add a new column to table

ALTER TABLE HumanResources.Department

ADD NoOfEmployees int null

GO

 

-- Update the new column from the GROUP BY derived query DG

-- DG JOINed with the update table - update group clause

-- Inner join group by - sql server update group by - SQL update using join

-- SQL update group by - MSSQL update table with group by

UPDATE DE

SET NoOfEmployees =DG.DeptEmployees

FROM HumanResources.Department DE

INNER JOIN (SELECT D.DepartmentID, COUNT(*) AS DeptEmployees

      FROM HumanResources.EmployeeDepartmentHistory EDH

      JOIN HumanResources.Department D

            ON d.DepartmentID = EDH.DepartmentID

            and EDH.EndDate is null

      JOIN HumanResources.Employee E

            ON EDH.EmployeeID = E.EmployeeID

      GROUP BY D.DepartmentID ) DG

ON DE.DepartmentID = DG.DepartmentID

GO

 

-- Check results

SELECT * FROM HumanResources.Department

GO

 

-- Cleanup - remove column from table

ALTER TABLE HumanResources.Department

DROP COLUMN NoOfEmployees

GO

 

-- SECOND SOLUTION: update with group by applying CORRELATED SUBQUERY

 

-- Add a new column to table

ALTER TABLE HumanResources.Department

ADD NoOfEmployees int null

GO

 

-- Update the new column from the GROUP BY correlated subquery

-- the WHERE clause defines the correlation

-- SQL group by within update

UPDATE DE

SET NoOfEmployees = (SELECT COUNT(*) AS DeptEmployees

      FROM HumanResources.EmployeeDepartmentHistory EDH

      JOIN HumanResources.Department D

            ON d.DepartmentID = EDH.DepartmentID

            and EDH.EndDate is null

      JOIN HumanResources.Employee E

            ON EDH.EmployeeID = E.EmployeeID

      WHERE DE.DepartmentID = D.DepartmentID

      GROUP BY D.DepartmentID )

FROM HumanResources.Department DE

GO

 

-- Check results

SELECT * FROM HumanResources.Department

GO

 

-- Cleanup - remove just-added column

ALTER TABLE HumanResources.Department

DROP COLUMN NoOfEmployees

GO

 

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

-- SQL update group by example for PO and Sales Order counts by year

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

USE tempdb;

GO

CREATE TABLE AWSummary (

ProfitCenterID int,

FiscalYear int,

POs int,

PODetails int,

SalesOrders int,

SalesOrderDetails int

)

GO

 

-- SQL insert with select

-- The SELECT uses two group by derived tables

-- Profit Center 1 is the entire company

-- The JOIN assumption is that the [Year]-s are matching

-- SQL inner join group by

INSERT AWSummary (ProfitCenterID, FiscalYear, POs, SalesOrders)

SELECT 1, po.[Year], POCount, SOCount

FROM

(

SELECT  [Year]=YEAR(OrderDate), POCount=COUNT(*)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

GROUP BY YEAR(OrderDate)

) po

INNER JOIN

(

SELECT  [Year]=YEAR(OrderDate), SOCount=COUNT(*)

FROM AdventureWorks.Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

) so

ON po.[Year] = so.[Year]

ORDER BY po.[Year]

GO

 

-- SQL update with group by for Purchase Orders Detail

-- SQL derived table join is used for the update

UPDATE AWSummary

      SET PODetails = Details

FROM AWSummary s

INNER JOIN

(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader poh

INNER JOIN AdventureWorks.Purchasing.PurchaseOrderDetail pod

on poh.PurchaseOrderID = pod.PurchaseOrderID

GROUP BY YEAR(OrderDate)

) d

ON d.Year = s.FiscalYear

WHERE s.ProfitCenterID = 1

GO

 

-- SQL update with group by for Sales Orders Detail

-- SQL derived table is used for the update

-- SQL update with join

UPDATE AWSummary

      SET SalesOrderDetails = Details

FROM AWSummary s

INNER JOIN

(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)

FROM AdventureWorks.Sales.SalesOrderHeader soh

INNER JOIN AdventureWorks.Sales.SalesOrderDetail sod

on soh.SalesOrderID = sod.SalesOrderID

GROUP BY YEAR(OrderDate)

) d

ON d.Year = s.FiscalYear

WHERE s.ProfitCenterID = 1

GO

 

-- Check results 

SELECT FiscalYear, POs, PODetails,  SalesOrders, SalesOrderDetails

FROM AWSummary

WHERE ProfitCenterID=1

ORDER BY FiscalYear

GO

/* Results

 

FiscalYear  POs         PODetails   SalesOrders SalesOrderDetails

2001        8           15          1379        5151

2002        272         629         3692        19353

2003        1035        2344        12443       51237

2004        2697        5857        13951       45576

*/

 

-- Cleanup

DROP TABLE AWSummary

GO

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

 

-- THIRD SOLUTION: update group by using a CTE - Common Table Expression

 

ALTER TABLE HumanResources.Department

ADD NoOfEmployees int null

GO

 

-- Update the new column from the GROUP BY cte

-- cte JOINed with the update table

-- SQL Server update group by

;WITH cteDeptCount AS

(SELECT D.DepartmentID, COUNT(*) AS DeptEmployees

      FROM HumanResources.EmployeeDepartmentHistory EDH

      JOIN HumanResources.Department D

            ON d.DepartmentID = EDH.DepartmentID

            and EDH.EndDate is null

      JOIN HumanResources.Employee E

            ON EDH.EmployeeID = E.EmployeeID

      GROUP BY D.DepartmentID )

UPDATE DE

SET NoOfEmployees =DC.DeptEmployees

FROM HumanResources.Department DE

INNER JOIN cteDeptCount DC

ON DE.DepartmentID = DC.DepartmentID

GO

 

-- Check results

SELECT * FROM HumanResources.Department

GO

 

-- Cleanup - remove just-added column

ALTER TABLE HumanResources.Department

DROP COLUMN NoOfEmployees

GO

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

 

-- FOURTH SOLUTION: update with SUBSELECT and GROUP BY

 

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

-- SQL update with subselect and group by

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

USE tempdb;

-- SQL select into create table for example

SELECT DISTINCT   SupervisorName=LastName+', '+FirstName,

                  SupervisorID=e2.EmployeeID, DirectlySupervise = 0

INTO DirectSupervisor

FROM AdventureWorks.HumanResources.Employee e1

INNER JOIN AdventureWorks.HumanResources.Employee e2

      ON e1.ManagerID = e2.EmployeeID

INNER JOIN AdventureWorks.Person.Contact c

      ON e2.ContactID = c.ContactID

ORDER BY SupervisorName

GO

-- SELECT * FROM DirectSupervisor

-- (47 row(s) affected)

 

SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()

GO

/* Partial results

 

SupervisorName                SupervisorID      DirectlySupervise

Kleinerman, Christian         49                0

Brown, Jo                     16                0

Kim, Shane                    159               0

Kane, Lori                    197               0

Wright, A. Scott              44                0

*/

-- SQL update with subselect

-- SQL Server update with subselect

UPDATE DirectSupervisor

SET DirectlySupervise = (SELECT COUNT(*)

                        FROM AdventureWorks.HumanResources.Employee e

                        WHERE E.ManagerID = DirectSupervisor.SupervisorID)

GO

-- (47 row(s) affected)

 

SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()

GO

/* Partial results

 

SupervisorName                      SupervisorID      DirectlySupervise

Krebs, Peter                        21                22

Sánchez, Ken                        109               6

Barreto de Mattos, Paula            30                5

Bradley, David                      6                 8

Welcker, Brian                      273               3

*/

 

-- Equivalent update with group by - SQL Server inner join group by

-- SQL Server update with group by

UPDATE dr

SET DirectlySupervise = sc.StaffCount

FROM DirectSupervisor dr

INNER JOIN (SELECT e.ManagerID, StaffCount=COUNT(*)

            FROM AdventureWorks.HumanResources.Employee e

            GROUP BY ManagerID) sc

      ON sc.ManagerID = dr.SupervisorID

GO

 

-- Cleanup

DROP TABLE tempdb.dbo.DirectSupervisor

GO

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

Related articles:

http://sqlusa.com/bestpractices2005/updatewithcorrelatedsubquery/

UPDATE (Transact-SQL)

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

 

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