>Hi -
>
>Here is an interesting problem. Suppose I have a statement like this:
>
>update SegmentAverages set
>p1 = (SELECT AVG(p1) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p2 = (SELECT AVG(p2) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p3 = (SELECT AVG(p3) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p4 = (SELECT AVG(p4) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p5 = (SELECT AVG(p5) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p6 = (SELECT AVG(p6) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p7 = (SELECT AVG(p7) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p8 = (SELECT AVG(p8) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p9 = (SELECT AVG(p9) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3)),
>p10 = (SELECT AVG(p10) FROM allcounts WHERE (allcounts.count_id = SegmentAverages.count_id and allcounts._day <3))
>
Try:
update SA set p1 = Av.P1, p2 = Av.P2, etc.
from SegmentAverages SA INNER JOIN (select Count_id, Avg(1.0*p1) as P1, Avg(1.0*P2) as P2, etc.
from AllCounts WHERE [_day] < 3 GROUP BY Count_ID) Av ON SA.count_id = Av.Count_id
If it's not broken, fix it until it is.
My Blog 
