3

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?

5

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
3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.