Delete all but top n from database table in SQL

What's the best way to delete all rows from a table in sql but to keep n number of rows on the top?

Solution 1:

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)

Edit:

Chris brings up a good performance hit since the TOP 10 query would be run for each row. If this is a one time thing, then it may not be as big of a deal, but if it is a common thing, then I did look closer at it.

Solution 2:

I would select ID column(s) the set of rows that you want to keep into a temp table or table variable. Then delete all the rows that do not exist in the temp table. The syntax mentioned by another user:

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)

Has a potential problem. The "SELECT TOP 10" query will be executed for each row in the table, which could be a huge performance hit. You want to avoid making the same query over and over again.

This syntax should work, based what you listed as your original SQL statement:

create table #nuke(NukeID int)

insert into #nuke(Nuke) select top 1000 id from article

delete article where not exists (select 1 from nuke where Nukeid = id)

drop table #nuke

Solution 3:

Future reference for those of use who don't use MS SQL.

In PostgreSQL use ORDER BY and LIMIT instead of TOP.

DELETE FROM table
WHERE id NOT IN (SELECT id FROM table ORDER BY id LIMIT n);

MySQL -- well...

Error -- This version of MySQL does not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Not yet I guess.

Solution 4:

I think using a virtual table would be much better than an IN-clause or temp table.

DELETE 
    Product
FROM
    Product
    LEFT OUTER JOIN
    (
        SELECT TOP 10
            Product.id
        FROM
            Product
    ) TopProducts ON Product.id = TopProducts.id
WHERE
    TopProducts.id IS NULL