How to delete the top 1000 rows from a table using Sql Server 2008?
I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I 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]
6 Solutions collect form web for “How to delete the top 1000 rows from a table using Sql Server 2008?”
The code you tried is in fact two statements. A
DELETE followed by a
You don’t define
TOP as ordered by what.
For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.
;WITH CTE AS ( SELECT TOP 1000 * FROM [mytab] ORDER BY a1 ) DELETE FROM CTE
May be better for sql2005+ to use:
DELETE TOP (1000) FROM [MyTab] WHERE YourConditions
DELETE FROM [MyTab] WHERE YourIdField IN ( SELECT TOP 1000 YourIdField FROM [MyTab] WHERE YourConditions )
If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to subquery:
DELETE FROM [MyTab] WHERE YourIdField IN ( SELECT TOP 1000 YourIdField FROM [MyTab] WHERE YourConditions ORDER BY ExplicitSortOrder )
Thanks tp @gbn for mentioning and demanding the more clear and exact answer.
As defined in the link below, you can delete in a straight forward manner
USE AdventureWorks2008R2; GO DELETE TOP (20) FROM Purchasing.PurchaseOrderDetail WHERE DueDate < '20020701'; GO
delete from [mytab] where [mytab].primarykeyid in ( select top 1000 primarykeyid from [mytab] )
It is fast. Try it:
DELETE FROM YourTABLE FROM (SELECT TOP XX PK FROM YourTABLE) tbl WHERE YourTABLE.PK = tbl.PK
YourTABLE by table name,
XX by a number, for example 1000,
pk is the name of the primary key field of your table.
SET ROWCOUNT 1000; DELETE FROM [MyTable] WHERE .....