Delete only 500 rows from a table

Question

Hi Friend,

Can somebody please, help me with a query to delete only 500 rows from a table which has 20000 rows. Also has to be older than a particular date.

Thanks for your help,

Soofy

Answer 1

You can use the Top keyword like you would in a select

Delete Top (500) 
From myTable
Where Date < '01/01/2009'

Answer 2

SET ROWCOUNT 500

DELETE FROM TableName WHERE TheDate < @YourDate

Answer 3

If you're using SQL Server 2005, you can do this:

DELETE TOP (500) FROM your_table
WHERE date_field < @my_particular_date

or you can do this:

SET ROWCOUNT 500

DELETE your_table
WHERE date_field < @my_particular_date

in SQL Server 2000, you can do this:

DELETE your_table
WHERE pk_field IN (
  SELECT TOP (500) * FROM your_table
  WHERE date_field < @my_particular_date
)

Answer 4

DELETE FROM Table_Name WHERE Primary_Key_Column IN (
    SELECT TOP 500 Primary_Key_Column FROM Table_Name WHERE [Date] < '01/01/2009'  ORDER BY Primary_Key_Column ASC
)

Answer 5

Top only works in Transact Sql, each sql has it's own version

For mySql and posGres

Delete 
From myTable
Where Date < '01/01/2009'
LIMIT 10;

For oracle:

SELECT 
FROM myTable 
WHERE Date < '01/01/2009'
and ROWNUM <= 10

Answer 6

The only thing I'll add is you probably want to use "ORDER BY [DATE] DESC" at the end of most of these queries. And if you have multiple matching dates, you'll want to add additional column ordering to get the correct values deleted.

This presumes of course, that you actually have a "createddate" or "modifieddate" you can use to sort by. You didn't specify whether it was the oldest created or the oldest unmodified. (ie if ID=1 is the oldest but I modified it yesterday should it still be deleted?) which you'll need to be careful with if you sort by the primary key - assuming you're using an incrementing primary key and not GUIDs or something...