Update Query With Count

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?

You need to do your aggregation separately in a CTE or derived table. Here's a CTE version that should work for you.

WITH    t1
          AS ( SELECT y.CS, COUNT(y.projs) AS records
                FROM es AS y
                GROUP BY y.CS)
     UPDATE x
        SET x.totalval = t1.records
        FROM Prs AS x
        LEFT JOIN t1
        ON  t1.CS = x.CS

StackExchange Database Administrators Q#171212, answer score: 6