Changing Data with a Cursor

The ADO, OLE DB, and ODBC application programming interfaces (APIs) support updating the current row on which the application is positioned in a result set. The fundamental process is to:

1. Bind the result set columns to program variables.
   
   
2. Execute the query.
   
   
3. Execute 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. Execute 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 using 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 Northwind
GO
DECLARE abc CURSOR FOR
SELECT CompanyName
FROM Shippers

OPEN abc
GO

FETCH NEXT FROM abc
GO

UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.'
WHERE CURRENT OF abc
GO

CLOSE abc
DEALLOCATE abc
GO

For information about joins, see Join Fundamentals.

See Also

UPDATE