CODEWITHSUNDEEP
sqlsql-serversql-server-2008
vivek
vivek

Reputation: 849

How to use cursor to update record

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

Upvotes: 16

Views: 108291

Answers (4)

Code
Code

Reputation: 749

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

Upvotes: 1

Toshihiko
Toshihiko

Reputation: 317

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;

Upvotes: -2

s1mm0t
s1mm0t

Reputation: 6095

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.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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;

Upvotes: 43

Related Questions