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?
Not really. The having clause implies an aggregation, which means you don't have the original rows any more.
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.
If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted.
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()
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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With