Update Query With Count

Question

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?

Answer 1

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

Answer 2

Try this:

UPDATE x
SET totalval = c.Countprojs
FROM 
 (SELECT 
  COUNT(projs) AS Countprojs
 FROM Prs x LEFT JOIN es y ON x.CS = y.CS
 GROUP BY x.CS
 ) c

Source