How do I delete a result in mysql where the number of matching entries is below a certail number

17
December 22, 2018, at 07:10 AM

I have the following query

select date, count(*) from table where date='2018-12-15' group by time having count(*) <241;

Which results in

+------------+----------+----------+
| date       | time     | count(*) |
+------------+----------+----------+
| 2018-12-15 | 00:05:00 |      116 |
| 2018-12-15 | 01:00:00 |      116 |
| 2018-12-15 | 06:45:00 |      125 |
| 2018-12-15 | 07:30:00 |      125 |
| 2018-12-15 | 08:00:00 |      125 |
| 2018-12-15 | 08:30:00 |      116 |
| 2018-12-15 | 12:20:00 |      125 |
| 2018-12-15 | 13:30:00 |      116 |
| 2018-12-15 | 21:00:00 |      116 |
| 2018-12-15 | 22:20:00 |      116 |
+------------+----------+----------+

I want to delete all these results. I tried the following but it isn't working :

delete from table where date='2018-12-15' group by time having count(*) <241;

What am I doing wrong?

Answer 1

You can use a join within the DELETE statement:

DELETE a FROM table a
JOIN 
(
  select time from table where date='2018-12-15' group by time having count(*) <241
) b ON a.time = b.time 
Answer 2

You can try this code

DELETE FROM table WHERE FIND_IN_SET(id, ( SELECT ids_to_delete FROM ( SELECT group_concat(id) AS ids_to_delete FROM table WHERE date='2018-12-15' GROUP BY time HAVING count() < 241 ) t ))

READ ALSO
Get latest timestamp of the top row where COUNT(*) is implemented in MySQL

Get latest timestamp of the top row where COUNT(*) is implemented in MySQL

I am in a strange situation where I am getting a count(*) for all rows from a table but want to get the timestamp of the last row when they are ordered

18
How can I prevent accumulating memory usage of a thread that reads from database in Python?

How can I prevent accumulating memory usage of a thread that reads from database in Python?

I have a code that uses a thread to periodically read data from a mysql database and make a dataframe of that dataOn a shorter interval it uses a thread to periodically use that data (in this example a simple print)

14
Mysql get data by year with pagination

Mysql get data by year with pagination

I need to get the data based on year with pagination,if the rows count is less,then search in next year

56