Background documentationUPDATE Clause (update_clause) Locate this document in the navigation structure

 

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

Structure

Syntax Syntax

  1. <update_clause> ::=
  FOR UPDATE [OF <column_name>,...] [NOWAIT]
End of the code.
Explanation

The specified column names must identify columns in the tables underlying the QUERY specification (QUERY expression). They do not have to occur in a selected column. The QUERY statement that contains the UPDATE clause must generate an updatable result table.

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

All columns of the underlying base tables are updatable if the user has the corresponding privileges, irrespective of whether they were specified as a column name or not. 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 as x = <expression>, where the expression (expression) contains the x column

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 fulfilled, the column name should not be specified.

NOWAIT

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 RequestTimeout).

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.

More Information

SELECT Statement (select_statement)

Concepts of the Database System, Locks, Restrictions for UPDATE Statements