批量更新不会结束，剩余数据不会更新

Question

我需要批量更新一个表，但它不起作用。我尝试了下面的两个选项。

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

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

请告知。

-选项#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

Answer 1

好吧，您的代码有几个问题。

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

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