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