CODEWITHSUNDEEP
sqlsql-serverdatabasedelete-row
Nick
Nick

Reputation: 2871

Deleting rows in a table a chunk at a time

I have a single-column table Ids, which whose column ID is of type uniqueidentifier. I have another table MyTable which has an ID column as well as many other columns. I would like to delete rows from MyTable 1000 at a time, where the ID from MyTable matches an ID in Ids.

WHILE 1 = 1 BEGIN
    DELETE t FROM (SELECT TOP 1000 ID FROM Ids) d INNER JOIN MyTable t ON d.ID = t.ID;
    IF @@ROWCOUNT < 1 BREAK;
    WAITFOR DELAY @sleeptime; -- some time to be determined later
END

This doesn't seem to work though. What should the statement actually be?

Upvotes: 4

Views: 18816

Answers (4)

Duncan Howe
Duncan Howe

Reputation: 3025

Try

Delete from MyTable 
Where ID in 
   (select top 1000 t.ID 
    from Ids t inner 
    join MyTable d on d.Id = t.Id)

Upvotes: 7

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

WHILE EXISTS (SELECT TOP 1 * FROM MyTable mt JOIN IDs i ON mt.ID = t.ID)
BEGIN

DELETE TOP (1000) FROM MyTable
FROM MyTable mt JOIN IDS i ON mt.ID = i.ID

--you can wait if you want, but probably not necessary
END

--Sorry for the quick post; was in a hurry :)

The DELETE statement in SQL Server supports two FROM clauses; the first FROM identifies the table that is having rows deleted, and the second FROM clause is used for JOINS.

See: http://msdn.microsoft.com/en-us/library/ms189835.aspx

Upvotes: 0

Mark
Mark

Reputation: 1098

Try this:

DECLARE @BatchSize INT
SET @BatchSize = 100000

WHILE @BatchSize <> 0
BEGIN 
   DELETE TOP (@BatchSize) t
    FROM [MyTable] t
    INNER JOIN [Ids] d ON d.ID=t.ID
    WHERE ????
   SET @BatchSize = @@rowcount 
END

Has the benefit that the only variable you need to create is the size, as it uses it for the WHILE loop check. When the delete gets below 100000, it will set the variable to that number, on the next pass there will be nothing to delete and the rowcount will be 0... and so you exit. Clean, simple, and easy to understand. Never use a CURSOR when WHILE will do the trick!

Upvotes: 14

E.J. Brennan
E.J. Brennan

Reputation: 46879

You could also try:

set rowcount 1000
delete from mytable where id in (select id from ids)
set rowcount 0  --reset it when you are done.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Upvotes: 2

Related Questions