0 votes
1 view
in SQL by (40.3k points)

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]

1 Answer

0 votes
by (40.3k points)

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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...