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
UPDATE [tblActor]
ReplyDeleteSET [RelativeScore] = (SELECT SUM(A.ntScore)
FROM [tblActor] A
WHERE A.vcActorCity = [tblActor].vcActorCity )
Nice Info
ReplyDelete