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.

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.

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.

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;