UPDATE (Searched)

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.

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 either 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 SELECT privilege is required on a searched update if the option for the SQL standard is set as follows:

  • For static SQL statements, if the SQLRULES(STD) bind option was specified
  • For dynamic SQL statements, if the CURRENT RULES special register is set to STD

If an expression that refers to a function is specified, the privilege set must include any authority that is necessary to execute the function.

Syntax

UPDATE {table-name | view-name}[correlation-name]
    SET assignment-clause 
    [WHERE search-condition]
    [WITH {CS | RR | RS}]
    [QUERYNO integer]

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.

When updating tables with subselects, the subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.

Invalid:

UPDATE tutorial.tutorial.practice 
    SET pay = pay + 5 WHERE e_no IN 
        SELECT e_no FROM debloc.debbie.employee 
            WHERE st = "MD";

Valid:

UPDATE tutorial.tutorial.practice 
    SET pay = pay + 5 
    WHERE e_no IN 
        SELECT e_no FROM tutorial.debbie.employee 
            WHERE st = "MD";

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.

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.

search-condition Preceded by the keyword WHERE, specifies a search condition for the optional WHERE clause.

Specifies an isolation level with which the statement is executed during a Searched UPDATE. You can override the isolation level of the statement using the WITH {RR | CS} clause. Because WITH UR only applies to read only operations, you cannot use it in an UPDATE statement. The WITH clause overrides the isolation level only for the statement in which it appears.

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.

Examples:

The following are examples of Searched UPDATE commands. The first example changes the value of the COLOR column for part number P2 to BROWN, and then adds 5 to its WEIGHT column value.

UPDATE part 
    SET color = "BROWN", weight = weight + 5 
    WHERE pno = "P2"

The next command doubles the STATUS column value of all London suppliers:

UPDATE supplier 
    SET status = status * 2 
    WHERE city = "LONDON"

The WHERE clause may also include a nested query, as in the following example, which updates the PARTSUPP table records containing the part numbers of any part made in Paris:

UPDATE partsupp 
    SET qty = qty-100 
    WHERE pno IN 
        SELECT pno 
            FROM part 
            WHERE city = "PARIS"

While an update command can only update one table, its WHERE clause may consist of a nested query that is a join of several tables. For example:

UPDATE supplier 
    SET status = 30 
    WHERE sno IN 
        SELECT sno 
            FROM partsupp, part 
            WHERE partsupp.pno = part.pno 
              AND qty > 50