How to delete the top 1000 rows from a table using Sql Server 2008?

Question

I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but instead of just deleting the top 1000 rows it deleted all the rows in the table.

Here is the code:

delete from [mytab] 

select top 1000 

a1,a2,a3

from [mytab]

Answer 1

It looks like you are trying to use two statements i.e. A DELETE followed by a SELECT. You can't define TOP as ordered by what.

For the specific ordering criteria deleting from a CTE or similar table expression is the most efficient way like this:

; WITH CTE AS

(

SELECT TOP 1000 *

FROM [mytab]

ORDER BY a1

)

DELETE FROM CTE