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:

Developer said...

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

Anonymous said...

Nice Info