Using update with Cursors

Considerations for using update with cursors.

You cannot update a scrollable cursor.
To update a row using a cursor, define the cursor with declare cursor, then open it. The cursor name cannot be a Transact-SQL parameter or a local variable. The cursor must be updatable, or the SAP ASE server returns an error. Any update to the cursor result set also affects the base table row from which the cursor row is derived.
The table_name or view_name specified with an update...where current of must be the table or view specified in the first from clause of the select statement that defines the cursor. If that from clause references more than one table or view (using a join), you can specify only the table or view being updated.

After the update, the cursor position remains unchanged. You can continue to update the row at that cursor position, provided another SQL statement does not move the position of that cursor.
The SAP ASE server allows you to update columns that are not specified in the list of columns of the cursor’s select_statement, but that are part of the tables specified in the select_statement. However, when you specify a column_name_list with for update, and you are declaring the cursor, you can update only those specific columns.
You can see the behavior of a cursor closing implicitly when the cursor is defined by a join operation, and another update command from the same client session deletes a row (updated in deferred mode) in the current position of the cursor. If:
- A searched or positioned update in an allpages-locked table changes a value of an index key – a subsequent fetch command may fail.
- A searched or positioned update in a data-only-locked table or data-row-locked table changes a value of an index key – the cursor remains positioned on the row, and the next fetch returns the next qualifying row.
  
  If the data-only-locked tables are semantic-partitioned tables and the udpate is being done in deferred mode, however, the row moves to a different partition as a result of the update, and a subsequent fetch command may fail.