You can update or delete a row returned by a cursor if the cursor is updatable. If the cursor is read-only, you can only read the data; you cannot update or delete it. By default, Adaptive Server attempts to determine whether a cursor is updatable before designating it as read-only.
You can explicitly specify whether a cursor is updatable by using the read only or update keywords in the declare statement. Specifying a cursor to be updatable ensures that Adaptive Server performs the positioned updates correctly. Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.
The following example defines an updatable result set for the pubs_crsr cursor:
declare pubs_crsr cursor for select pub_name, city, state from publishers for update of city, state
The above example includes all the rows from the publishers table, but it explicitly defines only the city and state columns for update.
Unless you plan to update or delete rows through a cursor, you should declare a cursor as read-only. If you do not explicitly specify read only or update, the cursor is implicitly updatable when the select statement does not contain any of the following constructs:
distinct option
group by clause
Aggregate function
Subquery
union operator
at isolation read uncommitted clause
You cannot specify the for update clause if a cursor’s select_statement contains one of the above constructs. Adaptive Server also defines a cursor as read-only if you declare certain types of cursors that include an order by clause as part of their select_statement. See “Types of cursors” for information on the types of cursors Adaptive Server supports.
