UPDATE is an executable command that can be issued interactively or embedded in a host language. UPDATE can be dynamically prepared.
Authority requirements for the UPDATE command depend on whether a table or view is being modified. If modifying a table, users need UPDATE privileges on (or ownership of) the table, DBADM authority on the database containing the table, or overall SYSADM or SYSCTRL (some catalog tables only) authority for the location. If updating a view, users need UPDATE privileges on the view or SYSADM authority for the location.
The Positioned UPDATE command can only be used with embedded SQL. In DB2 mode the cursor must have been defined with a FOR UPDATE OF clause (or the program precompiled with the NOFOR directive).
The syntax for the Positioned UPDATE appears below:
UPDATE {table-name | view-name} [correlation-name]
SET assignment-clause WHERE CURRENT OF cursor-name
[FOR ROW {host-variable | integer-constant} OF ROWSET]
assignment-clause:
{column-name = {expression | NULL | scalar-subselect}
| (column-name[,...] = {{expression | NULL} [,...] | row-select}}[,...]
| table-name or view-name | Identifies the table or view to be updated. If a view, the view must be updatable. The name must not identify:
|
| correlation-name | Can be used within search-condition or a positioned UPDATE to designate the table or view. |
In DB2 mode, you cannot perform an explicit update to null on a table column defined as NOT NULL WITH DEFAULT. For example:
UPDATE empl SET perf = NULL
returns an error message if the column named perf was originally declared as NOT NULL WITH DEFAULT.
The Searched UPDATE, command locates all records that satisfy the conditions specified in the WHERE clause (providing all index, integrity and check conditions are met and reverified). If the WHERE clause is omitted in a Searched UPDATE all records in the table will be updated. The WHERE search condition may be an expression or a nested query. Note that the UPDATE command cannot include an ORDER BY clause.
When performing a Positioned UPDATE, the WHERE CURRENT OF clause must specify the name of an open cursor. Only the current record (as defined in the opened cursor) will be updated, with the SET clause specifying the new values for the indicated columns.
Introduces a list of one or more column names and the values to be assigned to the columns.
| column-name | Identifies a column to be updated. column-name must identify a column of the specified table or view, but must not identify a ROWID column, an identity column that is defined
as GENERATED ALWAYS, or a view column that is derived from a scalar function, constant, or expression. The column names must
not be qualified, and the same column must not be specified more than once.
For a positioned update, allowable column names can be further restricted to those in a certain list. This list appears in the FOR UPDATE OF clause of the SELECT statement for the associated cursor. If the SELECT statement is dynamically prepared, the FOR UPDATE OF clause must always be present. A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same UPDATE statement. |
| expression | Indicates the new value of the column. Specifies an expression made up of constants, column-names, and arithmetic operators
+, -, *, / (or the keyword NULL) that will become the new value of column-name. It must not include a column function.
A column-name in an expression must identify a column of the table or view. For each row that is updated, the value of the column in the expression is the value of the column in the row before the row is updated. |
| NULL | Specifies the null value as the new value of the column. Specify NULL only for nullable columns. |
| scalar-subselect | Specifies a subselect that returns a single row with a single column. The column value is assigned to the corresponding column-name.
If the subselect returns no rows, the null value is assigned; an error occurs if the column to be updated is not nullable.
An error also occurs if there is more than one row in the result.
The subselect must not contain a GROUP BY or HAVING clause, and the subselect (or subquery within the subselect) cannot use the table or view being updated as its target. The subselect, however, can refer to columns of the table or view to be updated. The value of such a column in the subselect is the value of the column in the row before the row is updated. Correlated references to these columns are allowed only in a searched UPDATE and only in the search condition of the subselect. For example, the following syntax is valid: UPDATE TABLE T1
SET COL1 = (SELECT COUNT(*)
FROM TABLE2 T2
WHERE T2.COL5 = T2.COL3)
WHERE COL3 = 'ABC' |
| row-subselect | Specifies a subselect that returns a single row. The number of columns in the row must match the number of column-names that
are specified. The column values are assigned to each corresponding column-name. If the subselect returns no rows, the null
value is assigned; an error occurs if the column to be updated is not nullable. An error also occurs if there is more than
one row in the result.
The subselect must not contain a GROUP BY or HAVING clause, and the subselect (or subquery within the subselect) cannot use the table or view being updated as its target. The subselect, however, can refer to columns of the table or view to be updated. The value of such a column in the subselect is the value of the column in the row before the row is updated. Correlated references to these columns are allowed only in a searched UPDATE and only in the search condition of the subselect. |
| cursor-name | Preceded by the keywords WHERE CURRENT OF, identifies a previously declared and opened cursor. |
Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful for simplifying the use of optimization hints for access path selection, if hints are used.
Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful UPDATE statement. Until a commit or rollback operation releases the locks, only the application process that performed the insert can access the updated row. If LOBs are not updated, application processes that are running with uncommitted read can also access the updated row. The locks can also prevent other application processes from performing operations on the table. However, application processes that are running with uncommitted read can access locked pages and rows.
When a view is updated, the changes are reflected in the underlying database table. For a view to be updatable, the query used to define the view must satisfy the following criteria:
The following Positioned UPDATE statement changes the current PART table record (pointed to by the cursor PARTCUR) by setting the COLOR column to "BROWN" and the WEIGHT column to 25.
MOVE "BROWN" TO NEWCOLOR
MOVE 25 TO NEWWGT
EXEC SQL
UPDATE part
SET color = :NEWCOLOR, weight = :NEWWGT
WHERE CURRENT OF partcur
END-EXEC
The following Positioned UPDATE command changes the status of the supplier record (pointed to by the cursor supcur) to a value of ten:
newstatus = 10;
EXEC SQL
UPDATE supplier
SET status = :newstatus
WHERE CURRENT OF supcur;