Basic syntax on FOR UPDATE cursor

问题:

Ok, I'm certainly familiar with walking through a table using a read-only cursor, but I can't seem to find the right syntax for actually updating the current row (Neither the cursor page nor the UPDATE page in books online seems to show this simple operation):

DECLARE @counter int;
SET @counter = 1;
DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
WHILE @counter < 100
    FETCH NEXT FROM myCursor
    UPDATE myCursor SET Value = @Counter << DOESN'T WORK 
    SET @counter = @counter + 1
END
CLOSE myCursor
DEALLOCATE myCursor

I also tried just SET Value = @Counter and using an INTO @Value on the FETCH, but couldn't seem to get that to work either.

This is obviously over-simplified, there are much more efficient ways to just "count" down a column. I won't bore you with the actual calculation.

Yes, I do need a cursor and not an UPDATE on the entire table (the value for each successive row will be based on a calculation that depends on the prior row already being written).

Testing this initially on SQL 2005, but I will need to port the code to SQL 2000 and 2008 as well. Thanks!

回答1:

You want to use WHERE CURRENT OF -

DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

That is from MSDN.