UPDATE in Batches Does Not End and Remaining Data Does Not Get Updated

Question

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

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.

Please advise.

-- OPTION #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

-- OPTION #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

OK there were a couple of issues with your code.

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