May 10, 2010 at 8:51 am
I've run into this a few times - I have TableA that has a very large volume of rows added to it and a summary TableB table that shows row counts from TableA. I get a TSQL error when I use COUNT and GROUP BY in an UPDATE statement so here's what I use:
SELECT b.BatchID, b.UserID, COUNT(b.MessageID) ResultCount
INTO #BatchCount
FROM TableA a LEFT JOIN TableB b
ON a.UserID = b.UserID AND a.BatchID = b.BatchID
WHERE b.BatchID = 3
GROUP BY a.BatchID, a.UserID
UPDATE TableA SET ResultCount = bc.ResultCount
FROM TableA a JOIN #BatchCount bc
ON a.UserID = bc.UserID AND a.BatchID = bc.BatchID
So, basically, create a summary table with row counts and then update the TableB with those counts. What's the right way to do this with a single UPDATE statement rather than using a temp table?
Thanks!
Mark
May 10, 2010 at 11:03 am
You need to do the count (or any aggregation) as a SubQuery and join to it for the update.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy