How to use cursor to update record

Question

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

i think i can not use @@FETCH_STATUS as 1'st line as it is global.

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

This code updates all rows of `emp` for each iteration. When you are done all `empno` has the same value. — Mikael Eriksson, Dec 28 '11 at 06:35
Can you give us "before" and "after" data? you can do this in one single statement, no cursor... — gbn, Dec 28 '11 at 06:36
everytime you use a cursor, a SQL fairy goes up in flames! ;) — Mitch Wheat, Dec 28 '11 at 06:47

score 37 · Accepted Answer · answered Dec 28 '11 at 06:53

It looks like you want to assign an incremented value to empno starting with 10.

You can use a CTE and row_number() to do that. No need for a cursor.

;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;

I wish more answers like this. First thing was the "I think you should probably do it a better way", but to answer the question also include the actual answer too. — Denzil Newman, Nov 26 '20 at 11:02
This answer basically taught me several new tricks in one go. :) — vic, Mar 08 '21 at 19:18

score 1 · Answer 2 · answered Dec 28 '11 at 08:43

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:

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.

Another option could be to use a temporary table.

Code · Answer 3 · 2019-04-08T07:13:43.950

***/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

Toshihiko · Answer 4 · 2018-07-10T03:08:06.933

Just in case you're not using current of, you can use Row_Number function as a reference to update it.

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;

How to use cursor to update record

4 Answers4

Linked