Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider the following table:

mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | bigint(20)  | NO   | PRI | NULL              | auto_increment |
| pixel_id      | varchar(32) | NO   | MUL | NULL              |                |
| creation_time | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                |
| pixel         | mediumblob  | NO   |     | NULL              |                |
+---------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.04 sec)

With some data:

mysql> SELECT * FROM pixels;
+----+----------------------------------+---------------------+----------------------------------+
| id | pixel_id                         | creation_time       | pixel                            |
+----+----------------------------------+---------------------+----------------------------------+
|  1 | 0d1b042671e0f8c1d1f226abe923583c | 2012-07-01 14:42:26 | 2d8292a62e89fcbf8b1592cf53f0dc86 |
|  2 | 9192b7491ac9321ed67c198834965580 | 2012-07-01 14:42:26 | f41a4a3e1a5f2f25c02f2e377627355c |
|  3 | 82b6ad645a4c75a552c0ddfd8d07c38a | 2012-07-01 14:42:27 | bffd2be16fcb82d0592aaa00fe0ebb9d |
|  4 | de41f4932ee7e90bed2e26d4e7e1937a | 2012-07-01 14:42:27 | 6632df3642ce3465ee5160126f20d837 |
|  5 | f98ac2c09574e2accb6cff709ac8a97f | 2012-07-01 14:42:27 | 00d1a3d9e9b51d7e5f66120203189107 |
|  6 | e90a3233fd9054fb3c23d04b03a8dde8 | 2012-07-01 14:42:27 | 4d20a996a46b9767d8c3f6708cb0ce88 |
|  7 | 08177f9f44f3d6fa515bd1a1983a7b45 | 2012-07-01 14:42:28 | ed3a572da6d05d34f5928035bc67d5be |
|  8 | 9d9138ffb7df537d61276a91e837a327 | 2012-07-01 14:42:28 | 3be6876351254ffa4a00364cd3e8c10e |
+----+----------------------------------+---------------------+----------------------------------+

I'm trying to delete all the records except the last, say, 5 rows. "Last" is determined by id, as creation_time may be duplicate.

I've tried:

mysql> SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5;

And got the reasonable answer:

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

I've tried to use it as a subquery:

mysql> DELETE FROM pixels WHERE id < 
      (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5);
ERROR 1093 (HY000): You can't specify target table 'pixels' for update in FROM clause

Tried another approach:

mysql> DELETE FROM pixels WHERE id NOT IN 
      (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 5);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So, How do I delete all records except for the N most recent?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations.

If the id and creation_time always define the same ordering, you can use this:

DELETE p
FROM 
       pixels AS p
   JOIN
       ( SELECT id 
         FROM pixels 
         ORDER BY id       
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.id < lim.id ;

Since that may not be true, and the two orderings may sometimes differ, resulting in deleting more or fewer rows than 5, you could use this, which will be more accurate but probably slower with a big table:

DELETE p
FROM 
       pixels AS p
   JOIN
       ( SELECT creation_time, id 
         FROM pixels 
         ORDER BY creation_time DESC, id DESC
           LIMIT 1 OFFSET 4
       ) AS lim
     ON p.creation_time < lim.creation_time
     OR p.creation_time = lim.creation_time AND p.id < lim.id ;
share|improve this answer

Try this exclusion join which will delete any ids that are not matched by the subquery.

DELETE p FROM pixels p
LEFT JOIN 
  (SELECT id 
   FROM pixels
   ORDER BY creation_time DESC
   LIMIT 5) p2 USING(id) 
WHERE p2.id IS NULL;

Avoid NOT IN (SELECT...) in MySQL at (nearly) all costs. It executes the subquery for each row in the outer query. This has been fixed in the unreleased MySQL 5.6.

share|improve this answer
    
Discussion moved to chat –  Aaron Brown Jul 2 '12 at 0:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.