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

• Erik Reasonable Rates Darling: I think you may need to group by CS here, otherwise you're getting a count of all projects for the whole table.
• Shaulinator: Just ran a few tests, and that's a good point / what I experienced. Updating.
• Erik Reasonable Rates Darling: There you go, looks good.