Updating records using cursors

When a cursor is based on a join, only the (SELECT MAX(Rate Change Date) FROM Human Resources.

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 DECLARE @empno AS INT; DECLARE @Cur Emp No AS INT; SELECT @empno = 10; DECLARE employee_cursor CURSOR FOR SELECT empno FROM emp OPEN employee_cursor; FETCH NEXT FROM employee_cursor INTO @Cur Emp No 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 @Cur Emp No END; CLOSE employee_cursor; DEALLOCATE employee_cursor; Cursors are generally not a good solution, so any solution such as @Mikael Eriksson's is potentially better.

To demonstrate how to use a cursor to update records we'll create a running total of the number of Oscars our films have won.

updating records using cursors-27updating records using cursors-89updating records using cursors-32updating records using cursors-22

This has the benefit of doing most of the work in SQL and only updating based on the rowid, a unique address in the table.

Consider the following table: Here is how you use the Update Cursor.

I have highlighted the areas which differ compared with a normal cursor.

The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type: Using the code above will declare a cursor which can used to update records.

You can make things a little more obvious by explicitly stating that the cursor is to be used for updating, as shown below: This cursor can be used to update any field from the underlying data set.

Leave a Reply