UPDATE (Positioned)

The UPDATE command modifies the values of specified columns in rows of a table or view, if the view is updatable. Modifying a row of a view also modifies the underlying base table of the view. The UPDATE command statement has two configurations, depending on whether a searched update or positioned update is performed. A searched update modifies zero or more rows of a table. These updated rows can be optionally selected with a search condition. A positioned update modifies only one row -- derived as the current position of a previously declared and opened cursor. The table or view being updated can exist at the current location or at any other location accessible from the XDB Server.
Restriction: This topic applies to Windows environments only.

Invocation

UPDATE is an executable command that can be issued interactively or embedded in a host language. UPDATE can be dynamically prepared.

Authorization

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.

Syntax

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}}[,...]

Parameters:

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:
  • An auxiliary table
  • A created temporary table or a view of a created temporary table
  • A catalog table with no updatable columns or a view of a catalog table with no updatable columns
  • A read-only view.
correlation-name Can be used within search-condition or a positioned UPDATE to designate the table or view.

Explicit Update to Null (DB2 Mode)

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.

Description

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.

SET Clause

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 query cannot contain DISTINCT.
  • The SELECT clause can contain only column names (no expressions or functions).
  • The FROM clause can contain only one table name.
  • No GROUP BY or HAVING clauses are permitted.

Example:

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;