MySQL: Delete all but last N records Consider the following table:

mysql&gt; 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&gt; 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&gt; 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&gt; DELETE FROM pixels WHERE id &lt; 
      (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&gt; 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 &amp; IN/ALL/ANY/SOME subquery'


So, How do I delete all records except for the N most recent?
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 &lt; 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 &lt; lim.creation_time
     OR p.creation_time = lim.creation_time AND p.id &lt; lim.id ;