Aggregate NOT permitted in "set" list of Update

[BTPhila]

Can anyone tell me how to UPDATE a column w/ an aggregate value (sum) w/in an UPDATE statement?

I receive the error: "An aggregate may not appear in the set list of an UPDATE statement" in the follwoing code:

update TabAAA set A.col_777 = sum(A.Salary)
from TabAAA A TabBBB B,
where (A.col_1 = B.col_1)
and (A.col_2 = B.col_2)

[Bill]

BTPhila -- create a derived table in your update statement:

update TabAAA set col_777 = t1.SomeResult
from
(
select A.col_1,A.col_2,sum(A.Salary) as SomeResult
from TabAAA A inner join TabBBB B
on (A.col_1 = B.col_1)
and (A.col_2 = B.col_2)
group by A.col_1,A.col_2
) t1
inner join
TabAAA t2 on (t1.Col_1 = t2.Col_1) and (t1.Col_2 = t2.Col_2)


------------
BTPhila at 1/21/2002 11:32:06 AM

Can anyone tell me how to UPDATE a column w/ an aggregate value (sum) w/in an UPDATE statement?

I receive the error: "An aggregate may not appear in the set list of an UPDATE statement" in the follwoing code:

update TabAAA set A.col_777 = sum(A.Salary)
from TabAAA A TabBBB B,
where (A.col_1 = B.col_1)
and (A.col_2 = B.col_2)

[ABHAYMAINI]

please note that if you are trying to aggregate within that table then be careful in using joins with subquery,
dont mislead yourself in using self join, there will be no join in subquery in that case, i hope my following example explains that.

UPDATE Temp_Table_1
SET TOT = T1.SUMOFID
FROM
(
SELECT CLAIM_KY, SUM(CONVERT(INT,Temp_Table_1.EMPZIPIND)) AS SUMOFID FROM Temp_Table_1
GROUP BY CLAIM_KY
) T1
INNER JOIN Temp_Table_1 ON Temp_Table_1.CLAIM_KY = T1.CLAIM_KY