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. Adaptive Server attempts to use unique indexes for updatable cursors when scanning the base table. For cursors, Adaptive Server considers an index containing an IDENTITY column to be unique, even if it is not so declared.
Adaptive Server 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, Adaptive Server 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, Adaptive Server 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, Adaptive Server must use a unique index defined for one or more of the columns to scan the base table. If no unique index exists, Adaptive Server 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 table has only one unique index, you cannot include its column in the for update column_name_list; Adaptive Server 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 Adaptive Server 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, Adaptive Server needs at least one unique index key, not on the list, to perform a cursor scan.
Allowing Adaptive Server to use the unique index in the cursor scan in this manner helps to 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 Adaptive Server 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 Chapter 8, “Optimization for Cursors,” in the Performance and Tuning Series: Query Processing and Abstract Plans.
