Update with cursor not iterate in feed data-CodePudding

This is my update query with cursor. but it update all records of COM_Customer with first record in #TempTable and not iterate in #TempTable

DECLARE @CustomerNumber VARCHAR(50)

DECLARE db_cursor CURSOR FOR  
    SELECT CustomerNumber 
    FROM #TempTable

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @CustomerNumber   

WHILE @@FETCH_STATUS = 0   
BEGIN   
    PRINT @CustomerNumber

    UPDATE COM_Customer
    SET CustomerNumber = @CustomerNumber
    WHERE CustomerNumber IS NULL

    FETCH NEXT FROM db_cursor INTO @CustomerNumber   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

CodePudding user response：

but it update all records of COM_Customer with first record in #TempTable and not iterate in #TempTable

The query does iterate #TempTable. The PRINT @CustomerNumber will print out each value from #TempTable

The reason that it updates all with first value from #TempTable is because, the UPDATE query does exactly what it is supposed to do. It updates all rows with CustomerNumber IS NULL. So subsequent iteration does not have any rows with CustomerNumber IS NULL

UPDATE COM_Customer
SET    CustomerNumber = @CustomerNumber
WHERE  CustomerNumber IS NULL

Seems like you are updating arbitrary rows from #TempTable to COM_Customer, you can use ROW_NUMBER() to generate a sequence and used that to JOIN both tables. And without cursor at all.

UPDATE C
SET    CustomerNumber = T.CustomerNumber
FROM   (
            SELECT CustomerNumber, RN = ROW_NUMBER() OVER (ORDER BY CustomerNumber)
            FROM   COM_Customer
            WHERE  CustomerNumber IS NULL
       ) C
       INNER JOIN
       (
            SELECT CustomerNumber, RN = ROW_NUMBER() OVER (ORDER BY CustomerNumber)
            FROM   #TempTable
       ) T ON C.RN = T.RN

CodePudding user response：

Resolved:

DECLARE @CustomerNumber VARCHAR(50)
DECLARE db_cursor CURSOR FOR  
SELECT CustomerNumber FROM #TempTable
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @CustomerNumber   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT @CustomerNumber
   SET ROWCOUNT 1 --added
   Update COM_Customer
   set CustomerNumber = @CustomerNumber
   where CustomerNumber is null
   SET ROWCOUNT  0 --added
       FETCH NEXT FROM db_cursor INTO @CustomerNumber   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor