SQL 如何使用游标更新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8652653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use cursor to update record
提问by vivek
i am using below query to update my all records but it starts update from second row how can i modify it to do changes from 1'st row ?
我正在使用下面的查询来更新我的所有记录，但它从第二行开始更新我如何修改它以从第一行进行更改？
i am using mssql 2008
我正在使用mssql 2008
i think i can not use @@FETCH_STATUSas 1'st line as it is global.
我想我不能@@FETCH_STATUS用作第一行，因为它是全球性的。
thanks in advance
提前致谢
use vivdb
DECLARE @empno as int;
select @empno = 10;
DECLARE Employee_Cursor CURSOR FOR select * from emp
OPEN Employee_Cursor;
FETCH NEXT from Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
update emp set empno = @empno;
select @empno = @empno+1;
FETCH NEXT from Employee_Cursor
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
回答by Mikael Eriksson
It looks like you want to assign an incremented value to empno starting with 10.
看起来您想为 empno 分配一个从 10 开始的递增值。
You can use a CTE and row_number() to do that. No need for a cursor.
您可以使用 CTE 和 row_number() 来做到这一点。不需要游标。
;with C as
(
select empno,
9 + row_number() over(order by (select 1)) as NewEmpNo
from emp
)
update C
set empno = NewEmpNo
You cursor version could look like this to do the same.
您的光标版本可能看起来像这样来做同样的事情。
DECLARE @empno AS INT;
DECLARE @CurEmpNo AS INT;
SELECT @empno = 10;
DECLARE employee_cursor CURSOR FOR
SELECT empno
FROM emp
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @CurEmpNo
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE emp
SET empno = @empno
WHERE CURRENT OF employee_cursor;
SELECT @empno = @empno + 1;
FETCH NEXT FROM employee_cursor INTO @CurEmpNo
END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
回答by s1mm0t
Cursors are generally not a good solution, so any solution such as @Mikael Eriksson's is potentially better. However if you really have to use a cursor to do an update then you should mark it as insensitive:
游标通常不是一个好的解决方案，因此任何解决方案（例如 @Mikael Eriksson 的解决方案）都可能更好。但是，如果您确实必须使用游标进行更新，则应将其标记为不敏感：
DECLARE Employee_Cursor INSENSITIVE CURSOR FOR
SELECT empno FROM emp
I haven't confirmed this on 2008 but certainly on 2005 and below, you can expect all kinds of weirdness if you update the data your cursor is defined against, without marking the cursor as insensitive.
我还没有在 2008 年确认这一点，但肯定在 2005 年及以下，如果您更新光标所针对的数据，而不将光标标记为不敏感，您可以期待各种奇怪的事情。
Another option could be to use a temporary table.
另一种选择是使用临时表。
回答by Toshihiko
Just in case you're not using current of, you can use Row_Numberfunction as a reference to update it.
以防万一您不使用current of，您可以使用Row_Number函数作为参考来更新它。
DECLARE @empno AS INT;
DECLARE @CurEmpNo AS INT;
SELECT @empno = 10;
DECLARE employee_cursor CURSOR FOR
SELECT empno
FROM emp
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CurEmpNo = @CurEmpNo + 1, @empno = @empno + 1
;With C As(
Select empno,
ROW_NUMBER() OVER (ORDER BY empno DESC) AS RowNo
From emp
)
UPDATE C
SET empno = @empno
WHERE RowNo = @CurEmpNo;
FETCH NEXT FROM employee_cursor
END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
回答by JIYAUL MUSTAPHA
***/Update Multiple Record Using Curser In SQl /***
CREATE PROCEDURE [dbo].[SP_UpdateDatausingCursor_Update]
(
@Msg NVARCHAR(MAX)=null OUTPUT
)
AS
BEGIN
/**Declare Cursor**/
DECLARE @TCursor CURSOR
/**Declare Cursor**/
DECLARE @Code bigint=null,@ComId int=null,@JDate DATE=null,@LDate DATE=null
/**Creating TempDetails Table**/
CREATE TABLE #TempDetails
(Code BIGINT,ComId INT,JDate DATE,LDate DATE)
/**Creating TempDetails Table**/
INSERT INTO #TempDetails(Code,ComId,JDate,LDate)
(
SELECT DISTINCT Code,ComId,JDate,LDate FROM tbl_FormRecord /*GEt Data From table*/
)
SET @TCursor =CURSOR FOR SELECT Code,ComId,JDate,LDate FROM #TempDetails
OPEN @TCursor
FETCH NEXT FROM @TCursor INTO @Code,@ComId,@JDate,@LDate
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE tbl_Form2
SEt JDate=@JDate,
LDate=@LDate
WHERE Code=@Code AND ComId=@ComId
FETCH NEXT FROM @TCursor INTO @Code,@ComId,@JDate,@LDate
END
SET @Msg='DATE Updated Successfully.'
DEALLOCATE @TCursor
SELECT * FROM #TempDetails
DROP TABLE #TempDetails
END
