FOR UPDATE Clause

Use the FOR UPDATE clause in ESQL/C applications and in DB-Access when you intend to update the values returned by a prepared SELECT statement when the values are fetched.

Preparing a SELECT statement that contains a FOR UPDATE clause is equivalent to preparing the SELECT statement without the FOR UPDATE clause and then declaring a FOR UPDATE cursor for the prepared statement. This syntax fragment is part of the SELECT statement.
FOR UPDATE Clause
(1)
Notes:
  • 1 HCL OneDB™ ESQL/C and DB-Access only
Element Description Restrictions Syntax
column Name of a column that can be updated after a FETCH Must be in the FROM clause table, but it need not be in the Projection list. All columns must be from the same table. Identifier

The FOR UPDATE keywords notify the database server that updating is possible, causing it to use more stringent locking than it would with a Select cursor. You cannot modify data through a cursor without this clause. You can specify which columns can be updated.

After you declare a cursor for a SELECT . . . FOR UPDATE statement, you can update or delete the currently selected row using an UPDATE or DELETE statement with the WHERE CURRENT OF clause. The keywords CURRENT OF refer to the row that was most recently fetched; they replace the usual conditional expressions in the WHERE clause. To update rows with a specific value, your program might contain statements such as those in the following example: 
EXEC SQL BEGIN DECLARE SECTION;
    char fname[ 16];
    char lname[ 16];
    EXEC SQL END DECLARE SECTION;
. . .

 EXEC SQL connect to 'stores_demo';
 /* select statement being prepared contains a for update clause */
 EXEC SQL prepare x from 'select fname, lname from customer for update';
 EXEC SQL declare xc cursor for x; 

 for (;;)
   {
   EXEC SQL fetch xc into $fname, $lname;
   if (strncmp(SQLSTATE, '00', 2) != 0) break;
   printf("%d %s %s\n",cnum, fname, lname );
   if (cnum == 999)              --update rows with 999 customer_num
       EXEC SQL update customer set fname = 'rosey' where current of xc;
   }

 EXEC SQL close xc;
 EXEC SQL disconnect current;

A SELECT . . . FOR UPDATE statement, like an Update cursor, allows you to perform updates that are not possible with the UPDATE statement alone, because both the decision to update and the values of the new data items can be based on the original contents of the row. The UPDATE statement cannot query the table that is being updated.

Note: A normal update inside the FETCH loop of a cursor cannot guarantee that the updated rows are not fetched again after the UPDATE. The WHERE CURRENT OF specification relates the UPDATE to the Update cursor, and guarantees that each row is updated no more than once, by internally keeping a list of the rows that have already been updated. These rows will not be fetched again by the Update cursor.

Restrictions

A SELECT statement that includes the FOR UPDATE clause must conform to the following restrictions:
  • The statement can select data from only one table.
  • The statement cannot include any aggregate functions.
  • The statement cannot include any of the following clauses or keywords: DISTINCT, EXCEPT, FOR READ ONLY, GROUP BY, INTO TEMP, INTERSECT, INTO EXTERNAL, MINUS, ORDER BY, UNION, UNIQUE.
  • DECLARE statements that associate a cursor with the statement cannot also include the FOR UPDATE keywords.
  • The statement is valid only in ESQL/C routines and (within transactions) in the DB-Access utility. It cannot, for example, be issued within an SPL routine.

For information on how to declare an update cursor for a SELECT statement that does not include a FOR UPDATE clause, see Using the FOR UPDATE Option.

Update cursors in SPL routines

You cannot include the FOR UPDATE keywords in the SELECT . . . INTO segment of the FOREACH statement of SPL. An SPL routine, however, can provide the functionality of a FOR UPDATE cursor
  • by declaring a cursor name in the FOREACH statement,
  • and then using the WHERE CURRENT OF cursor clause in UPDATE or DELETE statements that operate on the current row of that cursor within the same FOREACH loop.