I am attempting to run the below update query
UPDATE x
SET totalval = COUNT(projs)
FROM Prs x
LEFT JOIN es y
ON x.CS = y.CS
However, I am getting the error:
Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
How should this statement be altered in order for it to be valid update statement?