Preparable dynamic UPDATE statement: locating Format 1 (Update data using a preprocessable cursor)
Function
Updates a specified column in the row pointed to by the cursor in the table. 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 1: Updating rows in a table by column, using a (preprocessable) cursor
UPDATE [[[RD-node-name.]authorization-identifier.]table-identifier [[AS] correlation-name]
[used-index-SQL-optimization-specification] ]
{SET {update-object = update-value
| (update-object, update-object[, update-object]) = row-subquery}
[, {update-object = update-value
| (update-object, update-object[, update-object]...) = row-subquery} ]...
| ADD repetition-column-name [{subscript | *}]
= {ARRAY [element-value[, element-value]...]
| ?-parameter}
[, repetition-column-name [{subscript | * } ]
= {ARRAY [element-value [, element-value]...]
| ?-parameter}]...
| DELETE repetition-column-name [{subscript | * }]
[, repetition-column-name [ {subscript | * } ] ]...}
WHERE CURRENT OF GLOBAL cursor-name
[WITH ROLLBACK]
update-object:: = {column-name|component-specification|column-name [{subscript | * } ] } |
Operands
For operands and operand rules other than update-value in the SET clause, ARRAY [element-value [, element-value] ...], and WHERE CURRENT OF GLOBAL cursor-name, see UPDATE statement Format 1.
- update-value
- The following items can be specified in update-value:
- Column name
- Component specification
- Literal
- Value expressions (including arithmetic and concatenation operations)
- Scalar subquery
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- NULL (represents the null value)
- DEFAULT (represents the default for the column to be updated)
- ? parameter
- ARRAY [element-value [, element-value]...]*
- * The following items can be specified in element-value:
- A column name other than a repetition column
- Subscripted repetition column
- Literal
- Value expressions (including arithmetic and concatenation operations)
- Scalar subquery
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- NULL (represents the null value)
- DEFAULT
- ? parameter
- Rules on update value
- An update value that includes an embedded variable, an indicator variable, an SQL variable, or an SQL parameter cannot be specified.
- Other update values are subject to the rules on update values of UPDATE statement Format 1.
- Rules on element value
- An element value that includes an embedded variable, an indicator variable, an SQL variable, or an SQL parameter cannot be specified.
- Other element values are subject to the rules on element values of UPDATE statement Format 1.
- ARRAY [element-value [, element-value]...]
- The following items can be specified as element-value:
- A column name other than a repetition column
- Subscripted repetition column
- Literal
- Value expressions (including arithmetic and concatenation operations)
- Scalar subquery
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP [(p)]
- NULL (represents the null value)
- DEFAULT
- ? parameter
- For rules on element values, see Rules on element values for the SET clause in UPDATE statement Format 1.
- WHERE CURRENT OF GLOBAL cursor-name
- GLOBAL
- Specifies GLOBAL as the scope of cursor-name.
- cursor-name
- This operand specifies the name of the cursor that points to the row to be updated.
- The cursor specified in cursor-name is one identified by the extended cursor name that was specified in the ALLOCATE CURSOR statement. However, a result set cursor cannot be specified.
- The cursor specified in cursor-name at run time must be open and positioned on the row to be updated.
- The position of the cursor specified in cursor-name remains unchanged after the execution of the UPDATE statement. If a row after the updated row is to be updated, the FETCH statement should be executed on the cursor to move it.
Common rules
- 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.
- 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.
- The common rules on UPDATE statement Format 1 are applicable to the other common rules.
Rules on referential constraints
- Rules on referential constraints for UPDATE statement Format 1 apply.
Examples
- Dynamically execute the SQL statement that updates the unit price (PRICE) for the row pointed to by the cursor (cr (scope: GLOBAL, value: CR1)) in the inventory table (STOCK) to a 10% discount.
PREPARE GLOBAL :sel FOR 'SELECT * FROM STOCK FOR UPDATE'
Assign CR1 to the embedded variable cr
ALLOCATE GLOBAL :cr CURSOR FOR GLOBAL :sel
PREPARE PRE1 FOR
'UPDATE SET PRICE = A value equal to a 10% discount on the unit price WHERE CURRENT OF GLOBAL CR1'
OPEN GLOBAL :cr
FETCH GLOBAL :cr INTO Name of the variable into which columns are fetched
EXECUTE PRE1
CLOSE GLOBAL :cr
DEALLOCATE PREPARE GLOBAL :sel