UPDATE CURRENT

Updates the current row indicated by a cursor.

 

Usage

Embedded, Module, ODBC, Procedural, JDBC.

Description

The current row addressed by the cursor is updated by assigning new values to the columns as specified in the SET clause.

See ALLOCATE CURSOR for a description of extended cursors.

If an extended cursor is used in an UPDATE CURRENT statement, the cursor is represented following these rules:

•If the UPDATE CURRENT statement is executed with static SQL, i.e. using EXEC SQL, the extended cursor is represented by the host variable containing the cursor.

•If the UPDATE CURRENT statement is executed with dynamic SQL, the extended cursor must be represented by the cursor value contained in the host variable.

Values to be assigned to columns may be specified either as expressions or by using the keywords NULL or DEFAULT. Expressions must have a data type compatible with the definition of the column to which they are assigned.

If column names are used in expressions, they must refer to columns in the table or view addressed in the UPDATE CURRENT clause. The value specified by a column name in an expression is the value for the column in the row concerned before the update operation is performed.

Language Elements

expression, see Expressions and Predicates.

with-clause, see The WITH Clause.

Restrictions

UPDATE access to the appropriate columns in the table or view identified by the table name is required when the cursor used for the UPDATE CURRENT statement is opened. If UPDATE access is not held, the cursor may be opened but UPDATE CURRENT statements will fail. Direct access to the base table is not required for an update operation on a view.

If the UPDATE CURRENT statement is used on a primary key column of a table, the table must be stored in a databank with the TRANSACTION or LOG option.

In a procedural usage context, extended-cursor-name cannot be used to identify the cursor.

In a procedural usage context, the UPDATE CURRENT statement is only permitted if the procedure access-clause is MODIFIES SQL DATA, see CREATE PROCEDURE.

A row indicated by a WITH HOLD cursor must have been fetched in the same transaction.

Notes

Column names on the left-hand side of the assignment operator in the SET clause may not be qualified by the table name.

Columns may not be specified more than once on the left-hand side of the assignment operator in the SET clause in a single UPDATE statement.

Expressions used in the SET clause cannot refer to set functions (except for in a subquery).

If columns are listed in the FOR UPDATE OF clause of the cursor definition (described under SELECT) no other columns may be specified on the left-hand side of the assignment operators in the SET clause.

The table name specified in the UPDATE CURRENT clause must be exactly the same as that in the FROM clause of the SELECT statement used to declare the cursor. If a synonym is used in one of the statements, the same synonym must also be used in the other.

UNIQUE constraints in the table being updated may not be violated.

If the table name specified in the UPDATE statement is subject to any referential constraint, the values in the row to be updated must conform to that constraint.

If a view defined WITH CHECK OPTION is to be updated, the values assigned to the columns must conform to the view definition.

The UPDATE CURRENT statement may not be used for read-only cursors.

Example

...

FETCH C1 INTO I_CHARGE_CODE,I_AMOUNT;

    IF I_CHARGE_CODE = '270' AND ... THEN

       UPDATE BILL SET AMOUNT = AMOUNT * 1.10 WHERE CURRENT OF C1;

...

Standard Compliance