dcsimg
Results 1 to 3 of 3

Thread: Aggregate NOT permitted in "set" list of Update

  1. #1
    BTPhila Guest

    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)

  2. #2
    Bill Guest

    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)

  3. #3
    Join Date
    Aug 2010
    Posts
    1

    Red face aggregate within a table

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •