CODEWITHSUNDEEP
sqlsql-serversql-delete
edgarmtze
edgarmtze

Reputation: 25048

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]

Upvotes: 154

Views: 404576

Answers (9)

Abdul Nasir Khayam
Abdul Nasir Khayam

Reputation: 337

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]

Upvotes: 4

Joseph Wambura
Joseph Wambura

Reputation: 3396

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

Upvotes: 1

yeOldeDataSmythe
yeOldeDataSmythe

Reputation: 843

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453142

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

Upvotes: 256

Rishi Bhatnagar
Rishi Bhatnagar

Reputation: 523

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

Upvotes: 35

Hamed elahi
Hamed elahi

Reputation: 31

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.

Upvotes: 3

Joe Bourne
Joe Bourne

Reputation: 1204

SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

Upvotes: 5

Oleg Dok
Oleg Dok

Reputation: 21766

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.

Upvotes: 141

Jason Dam
Jason Dam

Reputation: 400

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

Upvotes: 8

Related Questions