查询以删除100-100个批次中90天之前的数据

Question

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

Answer 1

所以您要将删除拆分为多个批次
然后您可能需要提交批处理事务。

删除语句可以简化。

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;

Answer 2

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

SET @Rows = @@ROWCOUNT

END