Determine Which Columns Can Be Updated

Scrollable cursors and insensitive nonscrollable cursors are read-only. If you do not specify a column_name_list with the for update clause, all the specified columns in the query can be updated. SAP ASE attempts to use unique indexes for updatable cursors when scanning the base table.

For cursors, SAP ASE considers an index containing an IDENTITY column to be unique, even if it is not so declared.

SAP ASE allows you to update columns in the column_name_list 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, you can update only the columns in that list.

In the following example, SAP ASE uses the unique index on the pub_id column of publishers (even though pub_id is not included in the definition of newpubs_crsr):

declare newpubs_crsr cursor
for select pub_name, city, state
from publishers
for update

If you do not specify the for update clause, SAP ASE chooses any unique index, although it can also use other indexes or table scans if no unique index exists for the specified table columns. However, when you specify the for update clause, SAP ASE must use a unique index defined for one or more of the columns to scan the base table. If no unique index exists, SAP ASE returns an error message.

In most cases, include only columns to be updated in the column_name_list of the for update clause. If the cursor is declared with a for update clause, and the table has only one unique index, you cannot include its column in the for update column_name_list; SAP ASE uses it during the cursor scan. If the table has more than one unique index, you can include the index column in the for update column_name_list, so that SAP ASE can use another unique index, which may not be in the column_name_list, to perform the cursor scan. For example, the table used in the following declare cursor statement has one unique index, on the column c3, so that column should not be included in the for update list:

declare mycursor cursor
for select c1, c2, 3
from mytable
for update of c1, c2

However, if mytable has more than one unique index, for example, on columns c3 and c4, you must specify one unique index in the for update clause as follows:

declare mycursor cursor
for select c1, c2, 3
from mytable
for update of c1, c2, c3

You cannot include both c3 and c4 in the column_name_list. In general, SAP ASE needs at least one unique index key, not on the list, to perform a cursor scan.

Allowing SAP ASE to use the unique index in the cursor scan in this manner helps prevent an update anomaly called the Halloween problem. The Halloween problem occurs when a client updates a column through a cursor, and that column defines the order in which the rows are returned from the base tables (that is, a unique indexed column). For example, if SAP ASE accesses a base table using an index, and the index key is updated by the client, the updated index row can move within the index and be read again by the cursor. The row seems to appear twice in the result set: when the index key is updated by the client and when the updated index row moves farther down the result set.

Another way to avoid the Halloween problem is to create tables with the unique auto_identity index database option set to on. See, Optimization for Cursors, in the Performance and Tuning Series: Query Processing and Abstract Plans.