Using Group By in an update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using Group By in an update statement

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-14 : 15:56:01

I understand that a normal UPDATE xxx FROM does not support the GROUP BY clause but for the life of me I can not figure out how to modify the below code to be in a single UPDATE statement. I now I could break it out into multiple update statements and do a subquery on each assignment but the over head on that is large and would like to avoid that.

    UPDATE #DriverTimes
    SET StartTime = MIN(dte.StrtTm),
        EndTime = MAX(dte.Endtm),
        #DriverTimes.StartBreak = MIN(dte.StartBreak),
        StartDateTime = (CONVERT(DATETIME, CONVERT(CHAR(8), dte.DtWrkd, 112) + ' ' + LEFT(RIGHT('0' + MIN(dte.StrtTm), 4), 2) + ':' + RIGHT(MIN(dte.StrtTm), 2))),
        EndDateTime =  (CONVERT(DATETIME, CONVERT(CHAR(8), dte.DtWrkd, 112) + ' ' + LEFT(RIGHT('0' + MIN(dte.EndTm), 4),2) + ':' + RIGHT(MIN(dte.EndTm), 2)))
    FROM dbo.spr_DriverTimeEntry AS dte WITH (NOLOCK)
    WHERE DriverID = DrvrID
    AND DateWorked = DtWrkd
    AND PryllID IN (
        1,
        22,
        1013,
        1014
    )
    GROUP BY DrvrID, Dtwrkd, PryllID;

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-14 : 16:07:49

Make it into a subquery or cte - like shown below:

;WITH cte AS
(
	SELECT 
		rvrID, Dtwrkd, PryllID,
		StartTime = MIN(dte.StrtTm),
		EndTime = MAX(dte.Endtm)
	FROM dbo.spr_DriverTimeEntry AS dt
	WHERE
		PryllID IN (
			1,
			22,
			1013,
			1014
    )
	GROUP BY
		DrvrID, Dtwrkd, PryllID
)
UPDATE d SET 
	StartTime = c.StartTime,
	EndTime = c.EndTime
FROM
	#DriverTimes d INNER JOIN cte c ON
		DriverID = DrvrID
		AND DateWorked = DtWrkd

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-11-14 : 16:40:30

Thanks, that looks to be working great.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources