Preparable dynamic UPDATE statement: locating Format 2 (Update data using a preprocessable cursor)

Function

Updates a specified column in the row pointed to by the cursor in the table with a FIX specification. This statement is used when updating is to be executed by the EXECUTE statement after preprocessing by a PREPARE statement or when preprocessing and execution are to be performed in a single operation using the EXECUTE IMMEDIATE statement.

Privileges

A user who has the UPDATE privilege for a table can update the column values in that table.

Format 2: Updating rows by row in a table with a FIX specification, using a (preprocessable) cursor

UPDATE [ [authorization-identifier.]table-identifier
     [IN (RDAREA-name-specification)][ [AS] correlation-name]
     [used-index-SQL-optimization-specification] ]
      SET ROW = row-update-value
     WHERE CURRENT OF GLOBAL cursor-name
     [WITH ROLLBACK]

Operands

For operands other than row-update-value in the SET clause and WHERE CURRENT OF GLOBAL cursor-name, and for operand rules, see UPDATE statement Format 2.

For details about the WHERE CURRENT OF GLOBAL cursor-name operand and about operand rules, see Preparable dynamic UPDATE statement: locating Format 1.

row-update-value
The following item can be specified as a row update value associated with ROW:
  • ? parameter

Common rules

  1. After preprocessing is performed by the PREPARE statement, an EXECUTE statement is used to execute, or an EXECUTE IMMEDIATE statement is used to preprocess and execute in a single operation.
  2. When omitting a table identifier, make sure that, before preprocessing is performed, the ALLOCATE CURSOR statement is used to allocate the cursor to the dynamic SELECT statement. In this operation, the table that is the object of retrieval specified in the dynamic SELECT statement to which the cursor is allocated is assumed. When specifying a table identifier, it is not necessary that the cursor be allocated to the dynamic SELECT statement before the preprocessing.
  3. The common rules on UPDATE statement Format 2 are applicable to the other common rules.

Rules on referential constraints

  1. Rules on referential constraints for UPDATE statement Format 2 apply.

Notes

  1. See the notes on UPDATE statement Format 2.

Example

  1. Dynamically execute the SQL statement that updates in a single operation the data in the row pointed to by the cursor (cr (value: CR1) in the inventory table (STOCK) with the contents of the embedded variable (XROW).

PREPARE GLOBAL :sel FOR 'SELECT * FROM STOCK FOR UPDATE'
Assigns CR1 to the embedded variable cr
ALLOCATE GLOBAL :cr CURSOR FOR GLOBAL :sel
PREPARE PRE1 FOR
   'UPDATE SET ROW = ? WHERE CURRENT OF GLOBAL CR1'
OPEN GLOBAL :cr
FETCH GLOBAL :cr INTO Name of the variable into which columns are fetched
EXECUTE PRE1 USING :XROW
CLOSE GLOBAL :cr
DEALLOCATE PREPARE GLOBAL :sel