sql - 更新行 par lot SQL Server

Question

我需要更改表中列的值，所以我需要这样做:

update members
set frequence = 1
where frequence <> 1

我有 700 万行受到影响，我需要分批更新 100000 行，然后休眠 2 分钟(复制到其他数据库的时间)。

请问我该怎么做？

注册

Answer 1

这是一种比较常见的方法，尽管通常它被用于limit the impact on transaction logs for large delete operations。 :

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;

WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;

    update top (100000) members
    set frequence = 1
    where frequence <> 1

    SET @r = @@ROWCOUNT;

    COMMIT TRANSACTION;

    WAITFOR DELAY '00:02'

    -- To limit log activity:
    -- CHECKPOINT;    -- if simple
    -- BACKUP LOG ... -- if full
END

如果您需要按顺序执行它们，请这样做:

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;

WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;

    ;with CTE as (
        select top 100000 *
        from members
        where frequence <> 1
        order by member_id
    )
    update CTE
    set frequence = 1

    SET @r = @@ROWCOUNT;

    COMMIT TRANSACTION;

    WAITFOR DELAY '00:02'

    -- To limit log activity:
    -- CHECKPOINT;    -- if simple
    -- BACKUP LOG ... -- if full
END