批量更新永远不会完成 [英] Update in Batches Never Finishes

问题描述

作为我的问题的后续问题，原始问题发布在这里

as a follow up on my question original question posted here

批量更新不会结束并且剩余数据不会得到更新

如果使用下面的逻辑，您将看到更新永远不会完成.让我知道您是否知道为什么...

If you use the logic below you'll see that update never finishes. Let me know if you have any ideas why...

表1

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

表2

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

    CREATE TABLE #Table1 (ID INT);

    DECLARE @Count int = 0;

    WHILE (select count(*) from #Table1) < 5000 BEGIN
        INSERT INTO #Table1 (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


     /****************** UPDATE ********************/

    select count (*) from #Table2 t2 where Exists (select * from #Table1 t1 where t1.ID = t2.ID)
    select count (*) from #Table2 where ID = 0
    select count (*) from #Table1 where ID = 0
       -- While exists an 'un-updated' record continue
    WHILE exists (select 1 from #Table2 t2 where Exists (select * from #Table1 t1 where t1.ID = t2.ID) ) 
    BEGIN

        -- Update any top 10 'un-updated' records
        UPDATE t2
        SET ID = 0
        FROM #Table2 t2
        WHERE ID IN (select top 10 id from #Table2 where Exists (select * from #Table1 t1 where t1.ID = t2.ID) )
    END

推荐答案

您的UPDATE语句引用了#Table2上的错误实例.您需要以下内容:

Your UPDATE statement is referencing the wrong instance on #Table2. You want the following:

UPDATE t2 SET
    ID = 0
FROM #Table2 t2
WHERE ID IN (
    SELECT TOP 10 ID
    -- note this alias is t2a, and is what the `exists` needs to reference
    -- not the table being updated (`t2`)
    FROM #Table2 t2a
    WHERE EXISTS (SELECT 1 FROM #Table1 t1 WHERE t1.ID = t2a.ID)
)

注意:为了进行测试，请确保@Count从1而不是0开始，否则您仍然会遇到无限循环.

Note: For testing ensure that @Count starts from 1 not 0 else you do still end up with an infinite loop.

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