Changing Rows with Positioned Operations

Updatable cursors support data modification statements that update rows through the cursor. When positioned on a row in an updatable cursor, you can perform update or delete operations that target the base table rows used to build the current row in the cursor. These are called position updates.

The positioned updates are performed on the same connection that opened the cursor. This allows the data modifications to share the same transaction space as the cursor, and prevents the updates from being blocked by locks held by the cursor.

There are two methods for performing positioned updates in a cursor:

The Transact-SQL WHERE CURRENT OF clause on an UPDATE or DELETE statement.
A database API positioned update function or method, such as the ODBC SQLSetPos function.

Performing Positioned Updates with Transact-SQL

The Transact-SQL WHERE CURRENT OF clause is typically used in Transact-SQL stored procedures, triggers, and scripts when modifications need to be made based on specific rows in a cursor. The stored procedure, trigger, or script will:

DECLARE and OPEN a cursor.
Use FETCH statements to get positioned on a row in the cursor.
Execute an UPDATE or DELETE statement using a WHERE CURRENT OF clause. Use the cursor_name from the DECLARE statement as the cursor_name in the WHERE CURRENT OF clause.

Performing Positioned Updates with APIs

Cursors created through the OLE DB, ADO, and DB-Library API functions and methods are not used in WHERE CURRENT OF clauses because they do not have names. ODBC, however, supports getting a name for an API server cursor with the SQLGetCursorName function. After setting the cursor attributes and opening a cursor by executing a Transact-SQL statement, use the SQLGetCursorName function to get a name for the cursor. After positioning in the cursor, execute an UPDATE or DELETE statement with a WHERE CURRENT OF clause referencing the name returned by SQLGetCursorName. This method is not recommended, however. It is better to use the positioned update functions in the ODBC API.

The database APIs support two different methods for performing positioned operations on API server cursors. ODBC and DB-Library share one model, OLE DB and ADO the other.

In ODBC and DB-Library, bind the columns in the cursor to program variables, then position on a specific row in a cursor. If performing a positioned update, change the data values in the program variables to the new values. Call these functions to perform the positioned operation:

ODBC: The SQLSetPos function
DB-Library: The dbcursor function

These functions have the following options:

SQLSetPos(SQL_POSITION): ODBC only, positions the ODBC cursor on a specific row in the current rowset.
SQLSetPos(SQL_REFRESH), dbcursor(CRS_REFRESH): Refreshes program variables bound to the result set columns with the values from the row the cursor is currently positioned on.
SQLSetPos(SQL_UPDATE), dbcursor(CRS_UPDATE): Updates the current row in the cursor with the values stored in the program variables bound to the result set columns.
SQLSetPos(SQL_DELETE), dbcursor(CRS_DELETE): Deletes the current row in the cursor.
dbcursor(CRS_LOCKCC): DB-Library only, locks the current row.

OLE DB and ADO use a different model to support positioned updates.

In OLE DB, when positioned on a row within the rowset, call the IRowsetChange::SetData or IRowsetChange::DeleteRows methods to perform positioned updates. If the OLE DB provider supports IRowsetUpdate::Update, the changes made with the IRowsetChange methods are cached until you call IRowsetUpdate::Update. If the OLE DB provider does not support IRowsetUpdate::Update, the changes made with the IRowsetChange methods are made immediately.

In ADO, when positioned on a row within the recordset, call the Recordset object’s Update or Delete methods to perform positioned updates. If the OLE DB provider supports IRowsetUpdate::Update, the changes made with the Recordset object’s Update or Delete methods are cached until you call the Recordset object’s UpdateBatch method. If the OLE DB provider does not support IRowsetUpdate::Update, the changes made with the Recordset object’s Update or Delete methods are made immediately.