-1

I want to delete all rows older than 30 days, but keep the newest 3 rows.

All rows newer than 30 days should stay.

Purpose: I have a db with messages. All messages older than 30 days should auto-delete (cron), but I want 3 messages to stay, so that users always know what the convo was about in case there are no messages newer than 30 days.

Deleting all rows older than 30 days is no problem, but I can't figure out how to exempt the 3 messages.

Mike
  • 29
  • 5

3 Answers3

1

You can use OFFSET to skip the first 3

DELETE FROM some_table 
WHERE dateColumn < now() - interval 30 DAY 
ORDER BY id DESC 
LIMIT 10000 OFFSET 3

(To test you can replace DELETE with a SELECT * to see what you're doing)
And an explanation why I've added LIMIT.

Martijn
  • 14,522
  • 4
  • 29
  • 61
0

There are probably a few ways of doing this. I thought of selecting the record's date you want to keep (you have definitely a timestamp in there), then delete everything from there:

BEGIN;
SET @keep = (SELECT at FROM t1 ORDER BY at DESC LIMIT 1 OFFSET 3);
DELETE t1 FROM traffic_foo WHERE at <= @keep;
COMMIT;

Might need a few tweaks for your schema.

geertjanvdk
  • 3,100
  • 20
  • 25
0

Delete operation will create free space and impact on performance if free space increased. So you need to optimize table time to time. if you want cron job for that operation. Then I recommend below steps:

  • insert into t1 select * from t1 order by id DESC limit 3;
  • rename table t1 to t1_tmp, t2 to t1;
  • drop table t1_tmp;

these will take less time to execute than delete operation.

ROHIT KHURANA
  • 539
  • 2
  • 11