Update Cursor Result Set Rows

You can use the where current of clause of the update statement to update the row at the current cursor position. Any update to the cursor result set also affects the base table row from which the cursor row is derived.

The syntax for update...where current of is:

update [[database.]owner.] {table_name | view_name} 
     set [[[database.]owner.] {table_name. | view_name.}]
          column_name1  =
               {expression1 | NULL | (select_statement)}
          [, column_name2  = 
               {expression2 | NULL | (select_statement)}]...
     where current of cursor_name

The set clause specifies the cursor’s result set column name and assigns the new value. When more than one column name and value pair is listed, separate them with commas.

The table_name or view_name 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 actually being updated.

For example, you can update the row that the pubs_crsr cursor currently points to:

update publishers
set city = "Pasadena",
    state = "CA"
where current of pubs_crsr

After the update, the cursor position remains unchanged. You can continue to update the row at that cursor position, as long as another SQL statement does not move the position of the cursor.

SAP ASE allows you to update columns that are not specified in the list of columns of the cursor’s select_statement, but are part of the tables specified in that statement. However, when you specify a column_name_list with for update, you can update only the columns in that list.