這是我的帶游標的更新查詢。但它 COM_Customer使用第一條記錄更新所有記錄#TempTable而不是迭代#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
uj5u.com熱心網友回復：
但它使用#TempTable 中的第一條記錄更新COM_Customer 的所有記錄，而不是在#TempTable 中迭代
查詢確實迭代#TempTable。將PRINT @CustomerNumber列印出每個值#TempTable
它使用第一個值更新所有內容的原因#TempTable是，UPDATE查詢完全按照它應該做的事情。它用 更新所有行CustomerNumber IS NULL。所以后續迭代沒有任何行CustomerNumber IS NULL
UPDATE COM_Customer
SET CustomerNumber = @CustomerNumber
WHERE CustomerNumber IS NULL
似乎您正在從#TempTableto更新任意行COM_Customer，您可以使用它ROW_NUMBER()來生成一個序列并將其用于JOIN兩個表。而且根本沒有游標。
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
uj5u.com熱心網友回復：
解決：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/410540.html
標籤：