Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Possible to do a delete with a HAVING clause?

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?

like image 628
Abe Miessler Avatar asked Jul 29 '12 01:07

Abe Miessler


People also ask

Can we use HAVING in DELETE statement?

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

Which SQL clause is used to DELETE?

The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Can DELETE work without WHERE clause?

If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted.

Can we use DELETE with GROUP BY?

MySQL Delete with Group By and Having clauses. To achieve this exploit with a single simple MySQL command, we are using two useful functions: 1. GROUP_CONCAT()


2 Answers

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) 
like image 136
Gordon Linoff Avatar answered Sep 24 '22 10:09

Gordon Linoff


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

like image 26
Zane Bien Avatar answered Sep 23 '22 10:09

Zane Bien