UPDATE Statement (update_statement) 

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

Syntax

<update_statement> ::=
  UPDATE [OF] <table_name> [<reference_name>] SET <set_update_clause>,...     [KEY <key_spec>,...] [WHERE <search_condition>] [IGNORE TRIGGER] [NOWAIT]
| UPDATE [OF] <table_name> [<reference_name>] (<column_name>,...)     VALUES (<extended_value_spec>,...) [KEY <key_spec>,...] [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>,...) WHERE CURRENT OF <result_table_name> [NOWAIT]

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

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 number of specified values extended_value_spec must be identical to the number of target columns. The i^th value specification is assigned to the i^th target column.

·        The current user must possess 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.

See also:

Data Type of the Target Column and Data Type of the Value to be Inserted

Which rows are updated?

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

·        Optional sequence of key specification and optional 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 containing the specified key values 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 in the result_table_name is specified: If the cursor is not positioned on a row in the results table, no rows are updated.

·        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.

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.

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

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:

...

       1.      Define the base table T_j containing the column to which v corresponds.

       2.      Define the unique table sequence Ti1...Tik that contains Tj.

       3.      Define last table Ti1 in this sequence that has a 1:1 relationship with the key table.

       4.      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.

       5.      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.

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.

CURRENT OF

If CURRENT OF is specified, the table name in the FROM clause of the QUERY statement used to construct the 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, but 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. 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 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.

Additional Information

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