How to delete older rows in mysql?

Question

I'm creating a log in the mysql database and I need to keep only latest 100 records and delete the older ones. I've tried the solution from THIS question but that doesn't seem to work for me.

my database table has the following columns: datetime temp humidity phvalue

I don't want to use the timestamp method because i need to keep a fixed number of records. can this be done in mysql?

score 1 · Answer 1 · answered Feb 03 '17 at 13:43

1

try this:

Delete from table t
where (Select count(*) from table 
      where datetime > t.DateTime) > 100

answered Feb 03 '17 at 13:43

Charles Bretana

131,932
22
140
207

score 1 · Answer 2 · answered Feb 03 '17 at 13:44

1

This is the solution.

DELETE FROM mytable WHERE id NOT IN (SELECT id FROM mytable ORDER BY id DESC LIMIT 100)

Inner select selects everything except newest 100 records (assuming id is auto increment) and excludes them in delete using NOT IN statement.

You can also use datetime instead of ID if datetime is in valid order.

answered Feb 03 '17 at 13:44

tilz0R

6,696
2
19
35

my table doesn't have 'ID' column. so i can't really use this method. – Suraj Bhawal Feb 03 '17 at 13:47
As mentioned in answer: "You can also use datetime instead of ID if datetime is in valid order." – tilz0R Feb 03 '17 at 13:47

score 0 · Answer 3 · answered Feb 03 '17 at 13:45

0

If you table has a primary key suppose p_id then you can use following query

DELETE FROM my_table WHERE p_id NOT IN (SELECT p_id FROM my_table ORDER BY id DESC LIMIT 100);

answered Feb 03 '17 at 13:45

Vikas Chaudhary

627
1
5
12

my table doesn't have a primary id. I've stated that in the question. – Suraj Bhawal Feb 03 '17 at 13:46
then you can use the datetime column instead of id, or it would be better if you can add a primary key to the table for more accurate result. – Vikas Chaudhary Feb 03 '17 at 13:47
Oh! ok.. thanks i'll try that. – Suraj Bhawal Feb 03 '17 at 13:49

score 0 · Answer 4 · answered Feb 03 '17 at 13:58

0

There is no other Column as the date field to find the oldest rows, but you can use the LIMIT clause to say how much rows to be deleted like this for the oldest 100 ROWS :

DELETE FROM table ORDER BY `datetime` DESC LIMIT 100;

answered Feb 03 '17 at 13:58

Bernd Buffen

12,768
2
20
31

How to delete older rows in mysql?

4 Answers4