批量更新不会结束，剩余数据不会更新 [英] UPDATE in Batches Does Not End and Remaining Data Does Not Get Updated

问题描述

我需要批量更新一个表，但它不起作用.我尝试了以下 2 个选项.

I need to update a table in batches, but it does not work. I tried 2 options below.

这两个选项都会更新前 10 行，但更新仍在运行.但只有 10 行保持更新.

Both of the options update the first 10 rows but the update is still running. But only 10 rows remain updated.

似乎更新永远不会完成，计数显示要更新的表中的记录数超过了.

Seems like update never finishes and count shows more than number of records in the tables to be updated.

请指教.

-- 选项 #1

SET NOCOUNT OFF

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
BEGIN 
    DROP TABLE #Table   
END

-- select count(*) from  #Table where ID = 0
-- select * from #Table

CREATE TABLE #Table ( ID INT )

WHILE (1 = 1)
    AND ( Select count(*) from #Table ) < 10000
BEGIN
    BEGIN TRANSACTION

    INSERT INTO #Table (ID) 
    VALUES (1)

    IF @@ROWCOUNT = 10000 -- terminating condition;
    BEGIN

    COMMIT TRANSACTION

    BREAK

    END
END

-- UPDATE
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION

    UPDATE TOP (10) upd
        SET ID = 0
    FROM #Table upd

    IF @@ROWCOUNT = 0 -- terminating condition;
    BEGIN

    COMMIT TRANSACTION

    BREAK

    END
END

-- 选项 #2

SET NOCOUNT OFF

IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
BEGIN 
    DROP TABLE #Table2  
END

-- select count(*) from  #Table2 where ID = 0
-- select * from  #Table2

CREATE TABLE #Table2 ( ID INT )

--DECLARE @rows INT
--DECLARE @count INT

WHILE (1 = 1)
    AND ( Select count(*) from #Table2 ) < 10000
BEGIN
    BEGIN TRANSACTION

    INSERT INTO #Table2 (ID) 
    VALUES (1)

    IF @@ROWCOUNT = 10000 -- terminating condition;
    BEGIN

    COMMIT TRANSACTION

    BREAK

    END
END

DECLARE @rows INT
DECLARE @count INT

-- UPDATE
SET @rows = 1
SET @count = 0

WHILE @rows > 0
BEGIN
    BEGIN TRANSACTION

    UPDATE TOP (10) #Table2 -- upd
        SET ID = 0
    --  FROM #Table upd

    SET @rows = @@ROWCOUNT
    SET @count = @count + @rows

    RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT

    COMMIT TRANSACTION
END

推荐答案

好的，您的代码存在一些问题.

OK there were a couple of issues with your code.

1. 您不能在更新中使用 TOP - 但是使用子查询限制行是相当直接的，如图所示.
2. 您将所有 ID 设置为 1，因此无法唯一标识一行，您只能更新所有 ID.我假设在您现实生活中的问题中您会有唯一的 ID，并且我已经修改了代码以适应.
3. 我不确定各种嵌套事务的意图，它们似乎没有完成太多工作，并且与逻辑不匹配.

1. You can't use TOP in an update - however its fairly straight forward to restrict the rows with a sub-query as shown.
2. You were setting all the ID's to 1 therefore there was no way to uniquely identify a row, you could only update all of them. I have assumed that in your real life problem you would have unique ID's and I have modified the code to suit.
3. I'm unsure about the intention of the various nested transactions, they don't appear to accomplish much and they don't match the logic.

    IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
    BEGIN 
        DROP TABLE #Table2;
    END

    CREATE TABLE #Table2 (ID INT);

    DECLARE @Count int = 0;

    WHILE (select count(*) from #Table2) < 10000 BEGIN
        INSERT INTO #Table2 (ID) 
        VALUES (@Count)

        -- Make sure we have a unique id for the test, else we can't identify 10 records
        set @Count = @Count + 1;
    END

    -- While exists an 'un-updated' record continue
    WHILE exists (select 1 from #Table2 where ID > 0) BEGIN
        -- Update any top 10 'un-updated' records
        UPDATE #Table2 SET
          ID = 0
        where id in (select top 10 id from #Table2 where ID > 0)
    END

    DROP TABLE #Table2  

这篇关于批量更新不会结束，剩余数据不会更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！