Looking for a non-RBAR solution to an update w/ aggregates problem
-
Hi folks,
What I am trying to do is update a table with the results of some aggregate functions in a non-RBAR fashion.
Currently what the code does is:
select Table1.ID as ID1, Count(Table2.ID as ID2) as MsgCount, Min(Table2.SomeDate) as StartDate, Max(Table2.SomeDate as EndDate)
from Table1 inner join Table2 on Table1.ID = Table2.Table1ID
then iterate through
update Table1 set MsgCount=@MsgCount, StartDate=@StartDate, EndDate=@EndDate where ID=@Table1ID
for each record...yuck!
What I would like to do is the following, except I can't since I can't use aggregates in an update "set":
update Table1
set MsgCount=Count(Table2.ID), StartDate=Min(Table2.SomeDate), EndDate=Max(Table2.SomeDate)
from Table1 inner join Table2 on Table1.ID=Table2.Table1ID
Can someone please suggest a good way to accomplish this in non-RBAR fashion?
Thanks in advance!
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant -
does this accomplish what you are looking to do?
with MessageCount (
ID1,
MsgCount,
StartDate,
EndDate
) as (
select
Table1.ID as ID1,
Count(Table2.ID as ID2) as MsgCount,
Min(Table2.SomeDate) as StartDate,
Max(Table2.SomeDate as EndDate
)
from
Table1
inner join Table2
on (Table1.ID = Table2.Table1ID)
)
update Table1 set
MsgCount = mc.MsgCount,
StartDate = mc.StartDate,
EndDate = mc.EndDate
from
Table1 t1
inner join MessageCount mc
on (t1.ID = mc.ID1)
-
Untested, but try this
with cte(ID,MsgCount,StartDate,EndDate) as (select Table1ID,count(*),min(SomeDate),max(SomeDate)
from Table2
group by Table1ID)
update t1
set MsgCount=c.MsgCount, StartDate=c.StartDate, EndDate=c.EndDate
from Table1 t1
inner join cte c on t1.ID = c.ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Visually it looks exactly like what I am trying to do, I will try it now.
Thanks!
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant -
Thanks again, this idea worked perfectly and gave me ideas for sprucing up some other processes as well. I had used CTEs for paging but not for bulk updates, so this turned on a lightbulb for me!
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant -
If you don't mind me asking, which version did you select?
-
Lynn, I started with yours but found I actually had to add the "group by" that Mark had in his since the Table1.ID wasn't part of any aggregate. I don't think there is much other difference between the two samples, clearly you were both thinking along the same lines.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant -
Looks like I deleted the group by from my original code as I cleaned it up for posting, sorry. But, yes, we were definately thinking along the same lines.
-
Anye Mercy (4/21/2009)
Thanks again, this idea worked perfectly and gave me ideas for sprucing up some other processes as well. I had used CTEs for paging but not for bulk updates, so this turned on a lightbulb for me!Glad we could help.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply