• Article

Changing Data by Using a Cursor

The ADO, OLE DB, and ODBC APIs support updating the current row on which the application is positioned in a result set. The following steps describe the fundamental process:

  1. Bind the result set columns to program variables.

  2. Execute the query.

  3. Call API functions or methods to position the application on a row within the result set.

  4. Fill the bound program variables with the new data values for any columns to be updated.

  5. Call one of these functions or methods to insert the row:

    • In ADO, call the Update method of the Recordset object.

    • In OLE DB, call the SetData method of the IRowsetChange interface.

    • In ODBC, call the SQLSetPos function with the SQL_UPDATE option.

When you use a Transact-SQL server cursor, you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause. Changes made with this clause affect only the row on which the cursor is positioned. When a cursor is based on a join, only the table_name specified in the UPDATE statement is modified. Other tables participating in the cursor are not affected.

USE AdventureWorks2008R2;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

For information about joins, see Join Fundamentals.