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 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]

Answer 1

The code you tried is in fact two statements. A DELETE followed by a SELECT.

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

Answer 2

May be better for sql2005+ to use:

DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions

For Sql2000:

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
)

BUT

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.

Answer 3

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

http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx

Answer 4

delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)

Answer 5

SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

Answer 6

It is fast. Try it:

DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK

Replace YourTABLE by table name, XX by a number, for example 1000, pk is the name of the primary key field of your table.

Answer 7

I agree with the Hamed elahi and Glorfindel.

My suggestion to add is you can delete and update using aliases

/* 
  given a table bi_customer_actions
  with a field bca_delete_flag of tinyint or bit
    and a field bca_add_date of datetime

  note: the *if 1=1* structure allows me to fold them and turn them on and off
 */
declare
        @Nrows int = 1000

if 1=1 /* testing the inner select */
begin
  select top (@Nrows) * 
    from bi_customer_actions
    where bca_delete_flag = 1
    order by bca_add_date
end

if 1=1 /* delete or update or select */
begin
  --select bca.*
  --update bca  set bca_delete_flag = 0
  delete bca
    from (
      select top (@Nrows) * 
        from bi_customer_actions
        where bca_delete_flag = 1
        order by bca_add_date
    ) as bca
end 

Answer 8

To enhance the accepted answer code, check out mine. The code will start by deleting 1000 rows from the table at a time, and then increase the batch size by 10% after each iteration. This way, the code will delete more rows as the table gets smaller and reduce the number of loops needed. The variable @count will still store the number of rows affected by each delete statement and the loop will stop when it becomes zero.

DECLARE @batch_size INT = 10000
DECLARE @count INT = 1
WHILE @count > 0
BEGIN
    ;WITH CTE AS
    (
    SELECT TOP (@batch_size) *
    FROM [mytab]
    ORDER BY a1
    )
    DELETE FROM CTE

    SET @count = @@ROWCOUNT

    -- Increase the batch size by 10% after each iteration
    SET @batch_size = @batch_size * 1.1
END

Answer 9

No need to create CTE to fetch and delete as it will load the records in memory first and then perform the delete operation which might impact the performance.

All you need is simple delete top(n) based on your condition to remove the records from your table which is fast and efficient for non-partitioned table.

delete top(1000)
from [mytab] 
where 
 condition[1]
 condition[2]
 .
 .
 condition[n]