Placing Indexes on ORDER BY Columns

When you include an ORDER BY clause in a SELECT statement, you can improve the performance of the query by creating an index on the column or columns that the ORDER BY clause specifies. The database server uses the index that you placed on the ORDER BY columns to sort the query results in the most efficient manner. For more information on how to create indexes that correspond to the columns of an ORDER BY clause, see Using the ASC and DESC Sort-Order Options.

Use the FOR UPDATE clause 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.

FOR UPDATE Clause:
 
|--FOR UPDATE--+----------------+-------------------------------|
               |     .-,------. |
               |     V        | |
               '-OF----column-+-'

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 does not need to be in the select list of the Projection clause	Identifier

The FOR UPDATE keyword notifies 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 words CURRENT OF refer to the row that was most recently fetched; they replace the usual test 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.