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

update members 
set frequence = 1 
where frequence <> 1 

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

请问我该怎么做？

注册

请您参考如下方法:

这是一种比较常见的方法，尽管通常它被用于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