May 5, 2014

An aggregate may not appear in the set list of an UPDATE statement sql server

If we will try to update a column of table with aggregate function:


UPDATE [tblActor]
     SET [RelativeScore] = (SELECT  SUM([tblActor].ntScore)
          FROM [tblActor] A

          WHERE A.vcActorCity = [tblActor].vcActorCity )

 

We may get error message something like:


Msg 157, Level 15, State 1, Line 2

An aggregate may not appear in the set list of an UPDATE statement.


Cause: We cannot use aggregate function in update statement as a sub query or join. 


Solution: Instead of sub query use Common table expression or derived table. For example:


--Using common table expression
;WITH cteAS(
     SELECT
          vcActorCity,
          SUM(ntScore) ntScoreSum FROM [tblActor]
     GROUP BY vcActorCity
)UPDATE [tblActor]
     SET [RelativeScore] = ntScoreSumFROM cteWHERE Cte.vcActorCity = [tblActor].vcActorCity


--Using derived table
UPDATE [tblActor]
     SET [RelativeScore] = ntScoreSum
FROM (
     SELECT
          vcActorCity,
          SUM(ntScore) ntScoreSum
     FROM [tblActor]
     GROUP BY vcActorCity) DT
WHERE DT.vcActorCity = [tblActor].vcActorCity

2 comments:

  1. UPDATE [tblActor]
    SET [RelativeScore] = (SELECT SUM(A.ntScore)
    FROM [tblActor] A
    WHERE A.vcActorCity = [tblActor].vcActorCity )

    ReplyDelete
  2. Anonymous5/28/2014

    Nice Info

    ReplyDelete