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:
- Bind the result set columns to program variables.
- Execute the query.
- Call API functions or methods to position the application on a row within the result set.
- Fill the bound program variables with the new data values for any columns to be updated.
- 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 AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
SELECT a.EmployeeID
FROM HumanResources.EmployeePayHistory AS a
WHERE RateChangeDate <>
(SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory AS b
WHERE a.EmployeeID = b.EmployeeID) ;
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.
See Also
Concepts
Changing Data by Using UPDATE
Changing ntext, text or image Data