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