Aggregate NOT permitted in "set" list of Update
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)
Aggregate NOT permitted in "set" list of Update (reply)
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)