sql - 如何使用游标更新记录

Question

我使用下面的查询来更新我的所有记录，但它从第二行开始更新
我如何修改它以从第一行开始更改？

我正在使用 mssql 2008

我想我不能用 @@FETCH_STATUS作为第一行，因为它是全局性的。

提前致谢

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

Answer 1

看起来您想为 empno 分配一个从 10 开始的递增值。

您可以使用 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

您的光标版本可能看起来像这样来做同样的事情。

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;