SQL query Delete all records from the table except latest N

Question

Is it possible to build a single mysql query  without variables  to remove all records from the table  except latest N  sorted by id desc    Something like this  only it doesn t work     delete from table order by id ASC limit   select count    from table   - N    Thanks

User · Answer

To delete all the records except te last N you may use the query reported below   It s a single query but with many statements so it s actually not a single query the way it was intended in the original question   Also you need a variable and a built-in  in the query  prepared statement due to a bug in MySQL   Hope it may be useful anyway     nnn are the rows to keep and theTable is the table you re working on   I m assuming you have an autoincrementing record named id  SELECT  ROWS TO DELETE    COUNT    - nnn FROM  theTable   SELECT  ROWS TO DELETE    IF  ROWS TO DELETE lt 0 0  ROWS TO DELETE   PREPARE STMT FROM  DELETE FROM  theTable  ORDER BY  id  ASC LIMIT     EXECUTE STMT USING  ROWS TO DELETE    The good thing about this approach is performance  I ve tested the query on a local DB with about 13 000 record  keeping the last 1 000  It runs in 0 08 seconds   The script from the accepted answer     DELETE FROM  table  WHERE id NOT IN     SELECT id   FROM       SELECT id     FROM  table      ORDER BY id DESC     LIMIT 42 -- keep this many records     foo      Takes 0 55 seconds  About 7 times more   Test environment  mySQL 5 5 25 on a late 2011 i7 MacBookPro with SSD

User · Answer

Why not  DELETE FROM table ORDER BY id DESC LIMIT 1  123456789   Just delete all but the first row  order is DESC    using a very very large nummber as second LIMIT-argument  See here

User · Answer

You cannot delete the records that way  the main issue being that you cannot use a subquery to specify the value of a LIMIT clause   This works  tested in MySQL 5 0 67    DELETE FROM  table  WHERE id NOT IN     SELECT id   FROM       SELECT id     FROM  table      ORDER BY id DESC     LIMIT 42 -- keep this many records     foo      The intermediate subquery is required  Without it we d run into two errors    SQL Error  1093   You can t specify target table  table  for update in FROM clause - MySQL doesn t allow you to refer to the table you are deleting from within a direct subquery  SQL Error  1235   This version of MySQL doesn t yet support  LIMIT  amp  IN ALL ANY SOME subquery  - You can t use the LIMIT clause within a direct subquery of a NOT IN operator    Fortunately  using an intermediate subquery allows us to bypass both of these limitations     Nicole has pointed out this query can be optimised significantly for certain use cases  such as this one   I recommend reading that answer as well to see if it fits yours

User · Answer

This should work as well  DELETE FROM  table   INNER JOIN       SELECT  id       FROM           SELECT  id           FROM  table           ORDER BY  id  DESC         LIMIT N       AS Temp   AS Temp2 ON  table   id     Temp2   id

User · Answer

try below query   DELETE FROM tablename WHERE id  lt   SELECT   FROM  SELECT  MAX id -10  FROM tablename   AS a    the inner sub query will return the top 10 value and the outer query will delete all the records except the top 10

User · Answer

I know I m resurrecting quite an old question  but I recently ran into this issue  but needed something that scales to large numbers well  There wasn t any existing performance data  and since this question has had quite a bit of attention  I thought I d post what I found  The solutions that actually worked were the Alex Barrett s double sub-query NOT IN method  similar to Bill Karwin s   and Quassnoi s LEFT JOIN method  Unfortunately both of the above methods create very large intermediate temporary tables and performance degrades quickly as the number of records not being deleted gets large  What I settled on utilizes Alex Barrett s double sub-query  thanks   but uses  lt   instead of NOT IN  DELETE FROM  test sandbox    WHERE id  lt         SELECT id     FROM         SELECT id       FROM  test sandbox        ORDER BY id DESC       LIMIT 1 OFFSET 42 -- keep this many records       foo      It uses OFFSET to get the id of the Nth record and deletes that record and all previous records  Since ordering is already an assumption of this problem  ORDER BY id DESC    lt   is a perfect fit  It is much faster  since the temporary table generated by the subquery contains just one record instead of N records  Test case I tested the three working methods and the new method above in two test cases  Both test cases use 10000 existing rows  while the first test keeps 9000  deletes the oldest 1000  and the second test keeps 50  deletes the oldest 9950    ----------- ------------------------ ----------------------                10000 TOTAL  KEEP 9000   10000 TOTAL  KEEP 50    ----------- ------------------------ ----------------------    NOT IN              3 2542 seconds         0 1629 seconds     NOT IN v2           4 5863 seconds         0 1650 seconds      lt   OFFSET           0 0204 seconds         0 1076 seconds    ----------- ------------------------ ----------------------   What s interesting is that the  lt   method sees better performance across the board  but actually gets better the more you keep  instead of worse

User · Answer

DELETE FROM table WHERE ID NOT IN  SELECT MAX ID  ID FROM table

User · Answer

DELETE  i1   FROM    items i1 LEFT JOIN                   SELECT  id         FROM    items ii         ORDER BY                 id DESC         LIMIT 20           i2 ON      i1 id   i2 id WHERE   i2 id IS NULL

User · Answer

If you need to delete the records based on some other column as well  then here is a solution   DELETE FROM articles WHERE id IN      SELECT id      FROM         SELECT id         FROM articles         WHERE user id    userId         ORDER BY created at DESC LIMIT 500  10000000  abc    AND user id    userId

User · Answer

If your id is incremental then use something like  delete from table where id  lt   select max id  from table -N

User · Answer

Just wanted to throw this into the mix for anyone using Microsoft SQL Server instead of MySQL   The keyword  Limit  isn t supported by MSSQL  so you ll need to use an alternative   This code worked in SQL 2008  and is based on this SO post  https   stackoverflow com a 1104447 993856  -- Keep the last 10 most recent passwords for this user  DECLARE  UserID int  SET  UserID   1004 DECLARE  ThresholdID int -- Position of 10th password  SELECT   ThresholdID   UserPasswordHistoryID FROM                       SELECT ROW NUMBER               OVER  ORDER BY UserPasswordHistoryID DESC  AS RowNum  UserPasswordHistoryID             FROM UserPasswordHistory             WHERE UserID    UserID           sub WHERE    RowNum   10  -- Keep this many records   DELETE  UserPasswordHistory WHERE    UserID    UserID          AND  UserPasswordHistoryID  lt   ThresholdID    Admittedly  this is not elegant  If you re able to optimize this for Microsoft SQL  please share your solution   Thanks

User · Answer

Answering this after a long time   Came across the same situation and instead of using the answers mentioned  I came with below -   DELETE FROM table name order by ID limit 10   This will delete the 1st 10 records and keep the latest records

User · Answer

Unfortunately for all the answers given by other folks  you can t DELETE and SELECT from a given table in the same query  DELETE FROM mytable WHERE id NOT IN  SELECT MAX id  FROM mytable    ERROR 1093  HY000   You can t specify target table  mytable  for update  in FROM clause  Nor can MySQL support LIMIT in a subquery   These are limitations of MySQL  DELETE FROM mytable WHERE id NOT IN     SELECT id FROM mytable ORDER BY id DESC LIMIT 1    ERROR 1235  42000   This version of MySQL doesn t yet support   LIMIT  amp  IN ALL ANY SOME subquery   The best answer I can come up with is to do this in two stages  SELECT id FROM mytable ORDER BY id DESC LIMIT n    Collect the id s and make them into a comma-separated string  DELETE FROM mytable WHERE id NOT IN      comma-separated string         Normally interpolating a comma-separate list into an SQL statement introduces some risk of SQL injection  but in this case the values are not coming from an untrusted source  they are known to be integer values from the database itself   note  Though this doesn t get the job done in a single query  sometimes a more simple  get-it-done solution is the most effective

User · Answer

What about    SELECT   FROM table del           LEFT JOIN table keep          ON del id  lt  keep id          GROUP BY del   HAVING count     gt  N    It returns rows with more than N rows before  Could be useful

User · Answer

DELETE FROM table WHERE id NOT IN       SELECT id FROM table ORDER BY id  desc LIMIT 0  10

User · Answer

Using id for this task is not an option in many cases  For example - table with twitter statuses  Here is a variant with specified timestamp field   delete from table  where access time  gt          select access time from                 select access time from table              order by access time limit 150000 1       foo

[sql] SQL query: Delete all records from the table except latest N?

Examples related to sql

Examples related to mysql