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
uj5u.com熱心網友回復：
所以你將洗掉分成批次。
然后您可能想要提交批處理事務。
并且可以簡化洗掉陳述句。
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;
uj5u.com熱心網友回復：
DECLARE @Rows INT = 100
WHILE (@Rows=100)
BEGIN
DELETE TOP (100)
FROM auditdata
WHERE
UPDATEDDATE<DATEADD(DAY,-90, GETDATE())
SET @Rows = @@ROWCOUNT
END
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/397831.html
標籤：sql sql-server 查询语句
上一篇：谷歌云部署容器虛擬機“命令”選項