CODEWITHSUNDEEP
sql-servert-sqlsql-scripts
xscape
xscape

Reputation: 3376

SQL Script - two delete statements

I found this code into our repository and I need to modify the statement. However, I am bit hesitant because I am wondering, why does it have two delete statements? I've tried to debug it, remove the first delete, it didnt work. I understand that it will run the delete per 100 rows. Is there an option that I can have the same behavior but only have one DELETE statement? TIA!

USE [DBQ] 

SET ROWCOUNT 100
DELETE FROM dbo.setting WITH(ROWLOCK) WHERE CreateDate < DATEADD(day, -90, GETDATE())

WHILE @@rowcount > 0
BEGIN
 SET ROWCOUNT 100
 DELETE FROM  dbo.setting WITH(ROWLOCK) WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

Upvotes: 1

Views: 91

Answers (3)

Pratheek
Pratheek

Reputation: 81

Add a variable and change its value inside the loop.

declare @Start bit = 0
WHILE (@Start = 0 OR @@rowcount > 0)
BEGIN
  Set @Start = 1

  DELETE top (100) 
  FROM  dbo.setting WITH(ROWLOCK) 
  WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

You can just do a fake select to get a @@rowcount

select 1 
WHILE @@rowcount > 0
BEGIN
  DELETE top (100) 
  FROM  dbo.setting WITH(ROWLOCK) 
  WHERE CreateDate < DATEADD(day, -90, GETDATE())
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can simply remove the rowcount:

DELETE FROM dbo.setting WITH(ROWLOCK)
    WHERE CreateDate < DATEADD(day, -90, GETDATE());

Note this from the documentation:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

The reason the code would be written this way is to batch the deletes -- deleting a lot of records can take a long time.

If you want to maintain the batch deletes, then do something like:

declare @num_deleted int;
set @num_deleted = 999;

WHILE (@num_deleted > 0)
BEGIN 
     DELETE todelete
     FROM (SELECT TOP (100) s.*
           FROM dbo.setting s WITH(ROWLOCK)
           WHERE CreateDate < DATEADD(day, -90, GETDATE())
          ) todelete;

     set @num_deleted = @@rowcount;
END;

Upvotes: 1

Related Questions