UPDATE Clause (update_clause)

The UPDATE clause (update_clause) specifies that a result table is to be updateable. The system sets exclusive locks for all rows that form this result table.


<update_clause> ::= FOR UPDATE [OF <column_name>,...] [NOWAIT]


The specified column names must identify columns in the tables underlying the QUERY specification. They do not have to occur in a selected column.

The QUERY statement that contains the UPDATE clause must generate an updateable result table.

The UPDATE clause is a prerequisite for using the result table with CURRENT OF <result_table_name> in the UPDATE statement, in the DELETE statement, and in the LOCK statement. The update clause is meaningless for other forms of the above mentioned SQL statements, as well as in interactive mode.

All columns of the underlying base tables are updateable if the user has the corresponding privileges, irrespective of whether they were specified as a column name.

For performance reasons, it is recommended to specify column names only if the cursor is to be used in an update statement.

Assume that the column x fulfills the following conditions:

?     x is contained in the primary key or an index

?     x is contained in the search condition of the QUERY statement

?     x is contained in a SET UPDATE clause of the UPDATE statement in the type x = <expression>, where the expression contains the column x.

If all of the conditions are fulfilled, it is essential that you specify the column x as a column name in the UPDATE clause.

If at least one of these conditions is not satisfied, the column name should not be specified.


If NOWAIT is not specified and a lock collision occurs, the system waits for the locked data object to be released (but only as long as is specified by the special database parameter REQUEST_TIMEOUT).

If NOWAIT is specified, the database system does not wait until another user has released a data object. Instead, it issues a return code if a collision occurs. If there is no collision, the requested lock is set.

See also:

SELECT Statement (select_statement)

Concepts of the Database System, Restrictions for UPDATE Statements