FOR UPDATE cursor的基本语法 [英] Basic syntax on FOR UPDATE cursor

问题描述

好吧，我当然很熟悉使用只读游标浏览表格，但我似乎找不到实际更新当前行的正确语法（< a href =http://msdn.microsoft.com/en-us/library/ms180169%28v=SQL.90%29.aspx =nofollow>光标页和 UPDATE page 在线书籍似乎显示这个简单的操作）：

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

我也尝试过 SET Value = @Counter 并使用 INTO @Value 在FETCH上，但似乎不能得到这样工作。

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.

是的，我需要一个游标，而不是整个表上的UPDATE（每个连续行的值将是基于取决于先前行已经被写入的计算）。

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).

最初在SQL 2005上测试，但是我需要将代码移植到SQL 2000和2008以及。谢谢！

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

推荐答案

您要使用 WHERE CURRENT OF -

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

这来自 MSDN 。

这篇关于FOR UPDATE cursor的基本语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！