2

My table has a TIME field.

I want to keep only 5 newest rows.

Can I delete the old rows without using SELECT?

I think logic should be something like this:

DELETE FROM tbl WHERE row_num > 5 ORDER BY TIME

How can I implement this in MySQL whitout using SELECT to get list of TIME values?

Paul
  • 23,002
  • 11
  • 77
  • 112
xander27
  • 2,661
  • 7
  • 24
  • 37

4 Answers4

3

Without proper ORDER BY clause, SQL result set have to be considered as unordered.

You have to provide a column to explicitly store your rows sequence numbers. This could be a time stamp or the auto_increment column of your table.

Please keep in mind you could have concurrent access to your table as well. What should be the expected behavior if someone else is inserting while you are deleting? As far as I can tell this could lead to situation where you keep only the "5 latest rows" + "those inserted on the other transaction".

If your have the time column for that purpose on your table and a PRIMARY KEY (or some other UNIQUE NOT NULL column) you could write:

DELETE tbl FROM tbl LEFT JOIN (SELECT * FROM tbl ORDER BY tm DESC LIMIT 5) AS k
    ON (tbl.pk) = (k.pk)
    WHERE k.`time` IS NULL;

If you have composite primary key (a,b) You could write:

DELETE tbl FROM tbl LEFT JOIN (SELECT * FROM tbl ORDER BY tm DESC LIMIT 5) AS k
    ON (tbl.a,tbl.b) = (k.a,k.b)
    WHERE k.tm IS NULL;
Sylvain Leroux
  • 44,729
  • 6
  • 86
  • 107
  • He has a time field already. I agree there's a potential race condition. – Paul Aug 07 '13 at 07:42
  • @Paul I'm not quite sure about that as is it spelled all caps like a reserved keyword... – Sylvain Leroux Aug 07 '13 at 07:44
  • Assuming time or TIME is a timestamp on each row, I'm more concerned that the docs for MySQL DELETE have a bare number after LIMIT (e.g. LIMIT 3) and not a window (e.g. LIMIT 10,15). So LIMIT 5,10000000000 might be invalid. http://dev.mysql.com/doc/refman/5.7/en/delete.html – Paul Aug 07 '13 at 07:48
  • 1
    He also specifically said he wanted to avoid SELECT clauses, but probably needs one. – Paul Aug 07 '13 at 07:49
  • It seems difficult to avoid both sub-select *and* (the rather inelegant) `LIMIT 5, biiiig_number` – Sylvain Leroux Aug 07 '13 at 08:01
  • I don't have a MySQL handy and was worried that LIMIT 5, big number might even be a syntax error, the docs only show LIMIT 5; – Paul Aug 07 '13 at 08:18
  • @Paul It was so inelegant that I didn't bother testing it. But you are right. `LIMIT 5,...` (*range limit*) is simply not allowed in `DELETE` query. – Sylvain Leroux Aug 07 '13 at 08:32
  • What if PK is from 2 or 3 fields? – xander27 Aug 07 '13 at 09:02
  • @xander27 I have updated my answer (1) because MySQL does not support `LIMIT` for `IN` sub-queries (2) to show you how to deal with composite primary keys. – Sylvain Leroux Aug 07 '13 at 09:41
0
DELETE FROM TBL
  WHERE ROW_NUM = (SELECT ROW_NUM FROM TBL LIMIT 6, 99999)
  ORDER BY TIME DESC;

This will delete records from 6, 7, 8, 9, 10, ....., 200005

Because LIMIT range starts here from 6 to 9999 records, means 200005

Naveen Kumar Alone
  • 7,142
  • 5
  • 32
  • 50
0

If you want to exclude the top 5 rows, use something like:

DELETE FROM table WHERE  primary_key IN 
(SELECT primary_key FROM table LIMIT 1 OFFSET 5,1000000)

100000 can be a very large no

Hemant_Negi
  • 1,555
  • 14
  • 22
0

Maybe this would be an alternative:

DELETE FROM tbl 
WHERE  primary_key NOT IN (SELECT primary_key 
                           FROM tbl 
                           ORDER BY time
                           DESC LIMIT 5)
Surasin Tancharoen
  • 4,044
  • 3
  • 29
  • 34