MySQL DELETE all but latest X records

mysql sql-delete records
user1022585 picture user1022585 · Jan 16, 2012 · Viewed 7.7k times · Source

I have a script that runs every hour on my php site. In that script I would like some kind of MySQL query to delete every record from a table but say the latest 50.

How would I do something like that? 

// pseudo code: like this?
DELETE from chat WHERE id = max (ID - 50)

Answer

Brissles picture Brissles · Jan 16, 2012

You could try using NOT IN:

EDIT for MySQL:

DELETE FROM chat WHERE id NOT IN ( 
  SELECT id 
  FROM ( 
    SELECT id 
    FROM chat 
    ORDER BY id DESC 
    LIMIT 50
  ) x 
);

This is for SQL-Server:

DELETE FROM chat WHERE id NOT IN 
    (SELECT TOP 50 id FROM chat ORDER BY id DESC)

Assuming higher values of id are always newer.

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
How can I roll back my last delete command in MySQL?

I accidentally deleted some huge number of rows from a table... How can I roll it back? I executed the query using PuTTY. I'll be grateful if any of you can guide me safely out of this...

Read More
How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id …

Read More