Back to Contents

SQL Positioned Updates

Summary:

See also: Transactions, Static SQL, Dynamic SQL, Result Sets, SQL Errors.

What is a Positioned Update?

When declaring a database cursor with a SELECT statement using a unique table and including the FOR UPDATE keywords, you can update or delete database rows by using the WHERE CURRENT OF keywords in the UPDATE or DELETE statements. Such an operation is called Positioned Update or Positioned Delete.

Some database servers do not support hold cursors (WITH HOLD) declared with a SELECT statement including the FOR UPDATE keywords. The SQL standards require 'for update' cursors to be automatically closed at the end of a transaction. Therefore, it is strongly recommended that you use positioned updates in a transaction block.

Do not confuse positioned update with the use of SELECT FOR UPDATE statements that are not associated with a database cursor. Executing SELECT FOR UPDATE statements is supported by the language, but you cannot perform positioned updates since there is no cursor identifier associated to the result set. 

To perform a positioned update or delete, you must declare the database cursor with a SELECT FOR UPDATE statement:

Then, start a transaction, open the cursor and fetch a row:

Finally, you update or delete the current row and you commit the transaction:

DECLARE

Purpose:

Use this instruction to associate a database cursor with a SELECT statement to perform positioned updates in the current connection.

Syntax:

DECLARE cid [SCROLL] CURSOR [WITH HOLD] FOR { select-statement | sid }

Notes:

  1. cid is the identifier of the database cursor.
  2. select-statement is a SELECT statement defined in Static SQL.
  3. sid is the identifier of a prepared SELECT statement including the FOR UPDATE keywords.

Usage:

DECLARE ... FOR UPDATE will define a cursor that can be used to do positioned updates and deletes with the WHERE CURRENT OF clause.

DECLARE must precede any other statement that refers to the cursor during program execution.

To perform positioned updates, the select-statement must include the FOR UPDATE keywords.

The scope of reference of the cid cursor identifier is local to the module where it is declared. Therefore, you must execute the DECLARE, UPDATE or DELETE instructions in the same module.

Use the WITH HOLD option carefully, because this feature is specific to IBM Informix servers. Other database servers do not behave as Informix does with such cursors. For example, if the SELECT is not declared FOR UPDATE, most database servers keep cursors open after the end of a transaction, but IBM DB2 automatically closes all cursors when the transaction is rolled back.

For more details, see the DECLARE instruction description in Result Sets Processing.

UPDATE ... WHERE CURRENT OF

Purpose:

Updates the current row in a result set of a database cursor declared for update.

Syntax:

UPDATE table-specification
   SET
       column = { variable | literal | NULL }
       [,...]
   WHERE CURRENT OF cid

Notes:

  1. table-specification identifies the target table (see UPDATE for more details).
  2. column is a name of a table column.
  3. variable is a program variable, a record or an array used as a parameter buffer to provide values.
  4. literal is any literal expression supported by the language.
  5. cid is the identifier of the database cursor declared for update.

Usage:

Use UPDATE ... WHERE CURRENT OF to modify the values of the row currently pointed by the associated FOR UPDATE cursor.

The UPDATE statement does not advance the cursor to the next row, so the current row position remains unchanged.

The scope of reference of the cid cursor identifier is local to the module where it is declared. Therefore, you must execute the DECLARE, UPDATE or DELETE instructions in the same module.

There must be a current row in the result set. Make sure that the SQL status returned by the last FETCH is equal to zero.

If the DECLARE statement that created the cursor specified one or more columns in the FOR UPDATE clause, you are restricted to updating only those columns in a subsequent UPDATE ... WHERE CURRENT OF statement.

DELETE ... WHERE CURRENT OF

Purpose:

Deletes the current row in a result set of a database cursor declared for update.

Syntax:

DELETE FROM table-specification
    WHERE CURRENT OF cid

Notes:

  1. table-specification identifies the target table (see DELETE for more details).
  2. cid is the identifier of the database cursor declared for update.

Usage:

Use DELETE ... WHERE CURRENT OF to remove the row currently pointed by the associated FOR UPDATE cursor.

After the deletion, no current row exists; you cannot use the cursor to delete or update a row until you re-position the cursor with a FETCH statement.

The scope of reference of the cid cursor identifier is local to the module where it is declared. Therefore, you must execute the DECLARE, UPDATE or DELETE instructions in the same module.

There must be a current row in the result set. Make sure that the SQL status returned by the last FETCH is equal to zero.

Examples

Example 1:

01 MAIN
02    DEFINE pname CHAR(30)
03    DATABASE stock
04    DECLARE uc CURSOR FOR
05      SELECT name FROM item WHERE key=123 FOR UPDATE
06    BEGIN WORK
07      OPEN uc
08      FETCH uc INTO pname
09      IF sqlca.sqlcode=0 THEN
10         LET pname = "Dummy"
11         UPDATE item SET name=pname WHERE CURRENT OF uc
12      END IF
13      CLOSE uc
14    COMMIT WORK
15    FREE uc
16 END MAIN