MSSQL Transact-SQL 分批删除几种方式

1.拼接要分批删除的ID

DELETE FROM A

FROM A AS spqh WITH(NOLOCK)
INNER JOIN (SELECT '672998' AS ID UNION SELECT '672997' UNION SELECT '672996') AS sp

ON spqh.ID = sp.ID

 

2.根据条件分批删除

DELETE FROM A
FROM A WITH(NOLOCK) WHERE CreateDT<DATEADD(dd,-30,GETDATE())

 

3.使用TOP批量删除

DELETE TOP (100) FROM A WHERE CreateDT<DATEADD(dd,-20,GETDATE()) AND OrderStatus=2

4.使用set rowcount 设置删除数量（set rowcount同样可以应用于查询，修改，删除操作）
set rowcount 3

delete A WHERE Name='小米'