Possible to do a delete with a HAVING clause?

Question

I want to do something like below:

DELETE UserPredictions   GROUP BY UserId   HAVING COUNT(*) < 500 

But I'm getting a syntax error. Is it even possible to do a delete with a HAVING clause in SQL Server or will I have to roll the counts up into a CTE and do a delete with a join?

Answer 1

Not really. The having clause implies an aggregation, which means you don't have the original rows any more.

I think you want the following:

DELETE from UserPredictions where UserId in (select UserId from UserPredictions group by UserId having count(*) < 500) 

Answer 2

You can use a joined subselect within the DELETE statement:

DELETE a FROM   UserPredictions a JOIN (     SELECT   UserId     FROM     UserPredictions     GROUP BY UserId     HAVING   COUNT(1) < 500 ) b ON a.UserId = b.UserId 

---

SQLFiddle Demo