Delete all but top n from database table in SQL

Question

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

Answer 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.

Answer 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