Using an Update Cursor

An update cursor permits you to delete or update the current row; that is, the most recently fetched row. The following example in IBM Informix ESQL/C shows the declaration of an update cursor:

EXEC SQL
   DECLARE names CURSOR FOR
      SELECT fname, lname, company
      FROM customer
   FOR UPDATE; 

The program that uses this cursor can fetch rows in the usual way.

EXEC SQL
   FETCH names INTO :FNAME, :LNAME, :COMPANY;

If the program then decides that the row needs to be changed, it can do so.

if (strcmp(COMPANY, "SONY") ==0)
   {
   EXEC SQL
      UPDATE customer
         SET fname = 'Midori', lname = 'Tokugawa'
         WHERE CURRENT OF names; 
   }

The words CURRENT OF names take the place of the usual test expressions in the WHERE clause. In other respects, the UPDATE statement is the same as usual, even including the specification of the table name, which is implicit in the cursor name but still required.

The Purpose of the Keyword UPDATE

The purpose of the keyword UPDATE in a cursor is to let the database server know that the program can update (or delete) any row that it fetches. The database server places a more demanding lock on rows that are fetched through an update cursor and a less demanding lock when it fetches a row for a cursor that is not declared with that keyword. This action results in better performance for ordinary cursors and a higher level of concurrent use in a multiprocessing system. (Programming for a Multiuser Environment discusses levels of locks and concurrent use.)

Updating Specific Columns

The following example has updated specific columns of the preceding example of an update cursor:

EXEC SQL
   DECLARE names CURSOR FOR
      SELECT fname, lname, company, phone
         INTO  :FNAME,:LNAME,:COMPANY,:PHONE FROM customer
   FOR UPDATE OF fname, lname
END-EXEC.

Only the fname and lname columns can be updated through this cursor. A statement such as the following one is rejected as an error:

EXEC SQL
   UPDATE customer
      SET company = 'Siemens'
      WHERE CURRENT OF names
END-EXEC.

If the program attempts such an update, an error code is returned and no update occurs. An attempt to delete with WHERE CURRENT OF is also rejected, because deletion affects all columns.

UPDATE Keyword Not Always Needed

The ANSI standard for SQL does not provide for the FOR UPDATE clause in a cursor definition. When a program uses an ANSI-compliant database, it can update or delete with any cursor.