Deleting rows in a table a chunk at a time

Question

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?

Answer 1

Try

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