SQL大表批量更新循环无法自动停止，应如何调整代码？

问题核心疏漏 #

你写的UPDATE语句没有添加任何过滤条件，已经更新过的行每次循环都会被重复处理，导致@@ROWCOUNT永远返回大于0的值，循环永远不会终止。
没有过滤的情况下，相同行被反复更新，也会出现累计更新计数超过表实际总记录数的情况。

修复方案 #

根据你的业务场景可以选择以下两种修复方式：

方式1：按字段状态过滤（适合col_name原值不会等于'xxxxxx'的场景） #

只需要在UPDATE语句里新增WHERE条件，只处理还未更新的行即可：

SET NOCOUNT ON;

DECLARE @rows INT, @count INT, @message VARCHAR(100);

SET @rows = 1; 
SET @count = 0;

WHILE @rows > 0
BEGIN
    BEGIN TRAN

        UPDATE TOP 100000 tblName
        SET col_name = 'xxxxxx'
        -- 新增过滤条件，仅处理未更新的行
        WHERE col_name <> 'xxxxxx' OR col_name IS NULL

        SET @rows = @@ROWCOUNT
        SET @COUNT = @count + @rows
        RAISERROR ('count %d', 0, 1, @count) WITH NOWAIT

    COMMIT TRAN
END

所有行更新完成后，UPDATE不会命中任何记录，@@ROWCOUNT返回0，循环自动退出。

方式2：按主键范围遍历（性能更优，适合千万级大表） #

如果你的表有自增主键（比如字段名为id），用范围遍历的方式可以避免每次循环都全表扫描未更新的行，执行效率更高：

SET NOCOUNT ON;

DECLARE @rows INT, @count INT, @currentId INT, @maxId INT;

SET @rows = 1; 
SET @count = 0;
-- 预取主键范围
SELECT @currentId = MIN(id), @maxId = MAX(id) FROM tblName;

WHILE @currentId <= @maxId
BEGIN
    BEGIN TRAN

        UPDATE tblName
        SET col_name = 'xxxxxx'
        WHERE id BETWEEN @currentId AND @currentId + 99999

        SET @rows = @@ROWCOUNT
        SET @COUNT = @count + @rows
        RAISERROR ('count %d', 0, 1, @count) WITH NOWAIT

        -- 主键偏移到下一个批次
        SET @currentId = @currentId + 100000

    COMMIT TRAN
END

内容的提问来源于stack exchange，提问作者rvphx