Using FOR UPDATE with a List of Columns

When you declare an update cursor, you can limit the update to specific columns by including the OF keyword and a list of columns. You can modify only those named columns in subsequent UPDATE statements. The columns need not be in the select list of the SELECT clause.

The next example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_notansi table: 
EXEC SQL declare name_curs cursor for
   select * from customer_notansi
   for update of fname, lname;

By default, unless declared as FOR READ ONLY, a Select cursor in a database that is ANSI compliant is an update cursor, so the FOR UPDATE keywords are optional. If you want an update cursor to be able to modify only some of the columns in a table, however, you must specify these columns in the FOR UPDATE OF column list.

The principal advantage to specifying columns is documentation and preventing programming errors. (The database server refuses to update any other columns.) An additional advantage is improved performance, when the SELECT statement meets the following criteria:
  • The SELECT statement can be processed using an index.
  • The columns that are listed are not part of the index that is used to process the SELECT statement.

If the columns that you intend to update are part of the index that is used to process the SELECT statement, the database server keeps a list of each updated row, to ensure that no row is updated twice. If the OF keyword specifies which columns can be updated, the database server determines whether or not to keep the list of updated rows. If the database server determines that the work of keeping the list is unnecessary, performance improves. If you do not use the OF column list, the database server always maintains a list of updated rows, although the list might be unnecessary.

The following example contains code that uses an update cursor with a DELETE statement to delete the current row.

Whenever the row is deleted, the cursor remains between rows. After you delete data, you must use a FETCH statement to advance the cursor to the next row before you can refer to the cursor in a DELETE or UPDATE statement. 
EXEC SQL declare q_curs cursor for
   select * from customer where lname matches :last_name for update;

EXEC SQL open q_curs;
for (;;)
{
   EXEC SQL fetch q_curs into :cust_rec;
   if (strncmp(SQLSTATE, "00", 2) != 0)
      break;

   /* Display customer values and prompt for answer */
   printf("\n%s %s", cust_rec.fname, cust_rec.lname);
   printf("\nDelete this customer? ");
   scanf("%s", answer);

   if (answer[0] == 'y')
      EXEC SQL delete from customer where current of q_curs;
   if (strncmp(SQLSTATE, "00", 2) != 0)
      break;
}
printf("\n");
EXEC SQL close q_curs;