Query to delete data before 90 days in 100-100 batches-CodePudding

DECLARE @DELETED_ROWS INT;
SET @DELETED_ROWS = 100

WHILE (@DELETED_ROWS > 0)
BEGIN
    WITH cte AS
    (
         SELECT
             DATEADD(DAY, -90, UPDATEDDATE) AS date, audit_id 
         FROM
             auditdata 
    ) 
    DELETE TOP (100) 
    FROM cte 
    WHERE audit_id <= (SELECT MIN(audit_id)   100 FROM cte) 

    SET @DELETED_ROWS = @@ROWCOUNT
END

CodePudding user response：

So you're splitting the delete into batches.
Then you might want to commit the batch transactions.

And the delete statement can be simplified.

SET NOCOUNT ON;
 
DECLARE @DELETED_ROWS INT = 1;
 
WHILE @DELETED_ROWS > 0
BEGIN
  BEGIN TRANSACTION;
 
  DELETE TOP (100)
  FROM auditdata
  WHERE UPDATEDDATE < DATEADD(DAY, -90, GetDate());
 
  SET @DELETED_ROWS = @@ROWCOUNT;
 
  COMMIT TRANSACTION;
END;

CodePudding user response：

DECLARE @Rows INT = 100
WHILE (@Rows=100)
BEGIN
    DELETE TOP (100)
    FROM auditdata
    WHERE 
        UPDATEDDATE<DATEADD(DAY,-90, GETDATE())
    
     SET @Rows = @@ROWCOUNT
    
 END