11

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)
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1022585
  • 13,061
  • 21
  • 55
  • 75
  • Can you add a timestamp field to your table? – O.O Jan 16 '12 at 20:47
  • If your ID column is an identity column with autoincrement of 1, you could do somthing like that: `DELETE FROM chat WHERE ID NOT IN(SELECT TOP 50 ID FROM chat ORDER BY ID DESC)` – pistipanko Jan 16 '12 at 20:51
  • In this case use: `DELETE FROM chat WHERE datecol NOT IN(SELECT TOP 50 datecol FROM chat ORDER BY datecol DESC)-- SQL Server` `DELETE FROM chat WHERE datecol NOT IN(SELECT datecol FROM chat ORDER BY datecol DESC LIMIT 50) --MySQL` – pistipanko Jan 16 '12 at 20:57
  • @user1022585 - I think you should delete based on a time span (between x and x), rather than the number of records. Is there some reason why it has to be the last 50, rather than a time period/span? – O.O Jan 16 '12 at 21:01
  • `#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` – user1022585 Jan 16 '12 at 21:02
  • possible duplicate of [SQL query: Delete all records from the table except latest N?](http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n) – e-sushi Nov 23 '13 at 03:43

3 Answers3

19

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.

Brissles
  • 3,833
  • 23
  • 31
2

NOT IN is inefficient. You can slightly modify the first option in the previous answer by @Mithrandir to make it look like this:

DELETE from chat WHERE id < 
  (SELECT id FROM 
    (SELECT id FROM chat ORDER BY id DESC LIMIT 50) t ORDER BY id ASC LIMIT 1));
esp
  • 7,314
  • 6
  • 49
  • 79
0

You could try something like this:

DELETE from chat WHERE id < (SELECT max(ID)-50 FROM chat)

This would work if your values for ID are incremented in steps of 1. Or you can use something like:

DELETE FROM chat WHERE id NOT IN 
    (SELECT id FROM ( SELECT ID FROM chat ORDER BY datetime_col DESC LIMIT 50) t ) -- mysql
Mithrandir
  • 24,869
  • 6
  • 50
  • 66