UPDATE Statement (update_statement)

The UPDATE statement (update_statement) changes column values in table rows.

Structure

Syntax Syntax

<update_statement> ::=
  UPDATE [OF] <table_name>
    [<reference_name>]
    SET <set_update_clause>,...
    [KEY <key_spec>,...] <!  This SQL clause is
          no longer recommended to be used and
          might be removed from future versions.  !>
    [WHERE <search_condition>]
    [IGNORE TRIGGER]
    [NOWAIT]
| UPDATE [OF] <table_name>
    [<reference_name>]
    SET <set_update_clause>,...
    WHERE CURRENT OF <result_table_name>
    [NOWAIT]
| UPDATE [OF] <table_name>
    [<reference_name>]
    (<column_name>,...)
    VALUES (<extended_value_spec>,...)
    [KEY <key_spec>,...]
    [WHERE <search_condition>]
    [IGNORE TRIGGER]
    [NOWAIT]
<!  This SQL clause is no longer recommended to be used
    and might be removed from future versions.  !>
| UPDATE [OF] <table_name>
    [<reference_name>]
    (<column_name>,...)
    VALUES (<extended_value_spec>,...)
    WHERE CURRENT OF <result_table_name>
    [NOWAIT]
<!  This SQL clause is no longer recommended to be used
    and might be removed from future versions.  !>

<set_update_clause> ::=
  <column_name> = <extended_expression>
| <column_name>,... = (<extended_expression>,...)
| (<column_name>,...) = (<extended_expression>,...)
| <column_name> = <subquery>
| (<column_name>,...) = <subquery>

End of the code.

Examples

SQL Tutorial, Inserting, Changing and Deleting Rows

Explanation

The table name must identify an existing base table, view table, or synonym.

Columns whose values are to be updated are known as target columns. You can specify one or more target columns plus new values for these columns after you specify the table name and, if required, the reference name.

All target columns must identify columns in the table specified, and each target column may only be listed once.
The current user must have the UPDATE privilege for each target column in the specified table. If the table name identifies a view table, it is possible that even the owner of the view table may not be able to update column values as the view table is not updateable.
If the table specified is a view table, only column values from rows in the underlying base tables for the view table are updated. The target columns of the table specified correspond to the columns in the underlying base tables of the view table in such cases. The term “target column” always refers to the corresponding column in the base table in the following description.

Which rows are updated?

You set which rows in the table are to be updated by specifying one of the following:

Optional key specification (key_spec) and optional search condition (search_condition)

Key specification and no search condition: A row with the specified key values already exists. The corresponding values are then assigned to the target columns in this row. No rows are updated if a row with the specified key values does not exist.

Key specification and a search condition: A row with the specified key values already exists. The search condition is applied to this row. If the search condition is satisfied, the corresponding values are assigned to the target columns in this row. No rows are updated if a row with the specified key values does not exist or if a search condition is applied to a row and not fulfilled.

No key specification and a search condition: The search condition is applied to each row in the specified table. The corresponding values are assigned to the target columns for all rows that satisfy the search condition.
When using CURRENT OF so that the cursor position within the result table result_table_name is specified: If the cursor is not positioned on a row of the result table, no row will be changed.
If none of the above are specified, all rows in the table specified are updated.
If no row is found that satisfies the conditions defined by the optional clauses, the following message appears: 100 row not found.

If the table name specifies a join view table, columns may exist that can only be updated together with other columns.

Key Specification (key_spec)

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

Values in key columns that have been defined by a user in a CREATE TABLE statement or ALTER TABLE statement can also be updated. If the implicitly-created key column SYSKEY exists, this cannot be updated.

Determining the Column Combination for a Column in a Join View Table

To determine the combination of columns for a given column v in a join view table, proceed as follows:

Define the base table Tj containing the column to which v corresponds.
Define the unique table sequence Ti1 ... Tik that contains Tj.
Define the last table Ti1 in this sequence that has a 1:1 relationship with the key table.
The columns in the join view table corresponding to the foreign key columns of Ti1 in the referential CONSTRAINT definition between Ti1 and Ti1+1 relevant for the join view table, are elements of the column combination.
All columns of the join view table corresponding to columns of the tables Ti1+1 ... Tik are elements of the column combination.

To update the column value for the relevant column, a value must be specified for each column in the column combination. This applies for all target columns that fulfill one of the following conditions:

The target columns are located in a base table that is not a key table of the join view table and does not have a 1:1 relationship with the join view table key table.
The target columns are foreign key columns of a referential CONSTRAINT definition that is relevant for the join view table.

SET <set_update_clause>

The expression in a SET UPDATE clause set_update_clause cannot contain a set function. The subquery must produce a results table with no more than one row. The number of columns must be equal to the number of target columns specified.

VALUES (<extended_value_spec>)

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

The number of specified values extended_value_spec must be identical to the number of target columns. The ith value specification is assigned to the ith target column.

IGNORE TRIGGER

You specify IGNORE TRIGGER to ensure that no UPDATE trigger is executed by the UPDATE statement. This prevents endless recursion if updated rows are updated again in the trigger. You can use IGNORE TRIGGER only within a CREATE TRIGGER statement.

CURRENT OF

If CURRENT OF is specified, the table name in the FROM clause of the QUERY statement used to construct the result table result_table_name, must be identical to the table name in the UPDATE statement.

If CURRENT OF is specified and the cursor is positioned on a row in the results table, the corresponding values are assigned to the target columns of the corresponding row. This is the row in the table specified in the FROM clause of the QUERY statement used to form the row of the results table. It is essential that the results table was specified using FOR UPDATE. It is impossible to predict whether or not the updated values in the corresponding row are visible the next time this row in the results table is accessed.

NOWAIT

If you specify NOWAIT, the system does not wait for the lock to be released where a lock collision occurs. Instead, it returns an error message immediately.

Reasons for an UPDATE Statement Failure

If CONSTRAINT definitions exist for base tables in which rows were updated using the UPDATE statement, each updated row is checked against the CONSTRAINT definitions. The UPDATE statement fails if this is not the case for one or more of the modified rows.

For each row in which the value of foreign key columns has been updated with the UPDATE statement, the database system checks whether each resulting foreign key exists as a key or as a value of an index defined with UNIQUE in the corresponding referenced table referenced_table. The UPDATE statement fails if this is the case for one or more of the modified rows.

For each row in which the value of a referenced column referenced_column of a referential CONSTRAINT definition is to be updated using the UPDATE statement, the database system checks whether there are rows in the corresponding foreign key table that contain the current column values as foreign keys. The UPDATE statement fails if this is the case for one or more rows.

If triggers are defined for base tables in which rows are to be updated with the UPDATE statement, for execution after an UPDATE statement, these are executed accordingly. The UPDATE statement will fail if one of these triggers fails.

Data Type LOB

The UPDATE statement can only be used to assign a value to columns with the data type LOB if it contains a parameter or NULL specification. The assignment of values to LOB columns is therefore only possible with some database tools.

More Information

Data Manipulation