批量更新永远不会完成

Question

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

批量更新未结束，剩余数据未更新

如果你使用下面的逻辑，你会发现更新永远不会结束。如果你有任何想法，请告诉我为什么。。。

表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

Answer 1

您的UPDATE语句在#Table2上引用了错误的实例。您想要以下内容：

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开始，否则您仍然会以无限循环结束。