Delete only 500 rows from a table

Question

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

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
)