Description

table-name or view-name
Identifies the table or view to be updated. The name must identify a table or view that exists at the current server, but it must not identify a catalog table, a view of a catalog table, or a read-only view. For an explanation of read-only views and updatable views, see CREATE VIEW.
correlation-clause
Can be used within search-condition or assignment-clause to designate the table or view. For an explanation of correlation-clause, see table-reference. For an explanation of correlation-name, see Correlation names.
OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
Specifies whether system-generated values or user-specified values for a ROWID or identity column are used. If OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of columns in the SET clause must contain a column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit list of columns for the INSERT statement must contain a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
OVERRIDING SYSTEM VALUE
Specifies that the value specified in the SET clause for a column that is defined as GENERATED ALWAYS is used. A system-generated value is not used.
OVERRIDING USER VALUE
Specifies that the value specified in the SET clause for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is used, overriding the user-specified value.

If neither OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE is specified:

SET
Introduces the assignment of values to column names.
assignment-clause
column-name
Identifies a column to be updated. The column-name must identify a column of the specified table or view, but must not identify a view column derived from a scalar function, constant, or expression. A column must not be specified more than once.

For a Positioned UPDATE:

  • If the UPDATE clause was specified in the SELECT statement of the cursor, each column name in the SET list must also appear in the UPDATE clause.
  • If the UPDATE clause was not specified in the SELECT statement of the cursor, the name of any updatable column may be specified.

For more information, see update-clause.

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.

If a list of column-names is specified, the number of expressions, NULLs, and DEFAULTS must match the number of column-names.

ROW
Identifies all the columns of the specified table or view. If a view is specified, none of the columns of the view may be derived from a scalar function, constant, or expression.

The number of expressions, NULLs, and DEFAULTs (or the number of result columns from a row-fullselect) must match the number of columns in the row.

For a Positioned UPDATE, if the UPDATE clause was specified in the SELECT statement of the cursor, each column of the table or view must also appear in the UPDATE clause. For more information, see update-clause.

ROW may not be specified for a view that contains a view column derived from the same column as another column of the view, because both columns cannot be updated in the same UPDATE statement.

expression
Specifies the new value of the column. The expression is any expression of the type described in Expressions. It must not include an aggregate function.

A column-name in an expression must name a column of the named table or view. For each row updated, the value of the column in the expression is the value of the column in the row before the row is updated.

Each variable in the clause must identify a host structure or variable that is declared in accordance with the rules for declaring host structures and variables. In the operational form of the statement, a reference to a host structure is replaced by a reference to each of its variables. For further information on variables and structures, see References to host variables and Host structures. If a host structure is specified, the keyword ROW must be specified.

NULL
Specifies the new value for a column is the null value. NULL should only be specified for nullable columns.
DEFAULT
Specifies that the default value is assigned to a column. The value that is used depends on how the column was defined, as follows:
  • If the WITH DEFAULT clause is used, the default used is as defined for the column (see default-clause in column-definition in CREATE TABLE).
  • If the WITH DEFAULT clause or the NOT NULL clause is not used, the value used is NULL.
  • If the NOT NULL clause is used and the WITH DEFAULT clause is not used or DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that column.
row-fullselect
A fullselect that returns a single result row. The number of result columns in the select list must match the number of column-names (or if ROW is specified, the number of columns in the row) specified for assignment. The result column values are assigned to each corresponding column-name. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.

The row-fullselect may contain references to columns of the target table of the UPDATE statement. For each row updated, the value of such a column in the expression is the value of the column in the row before the row is updated.

WHERE
Specifies the rows to be updated. The clause can be omitted, or a search-condition or cursor-name can be specified. If the clause is omitted, all rows of the table or view are updated.
search-condition
Is any search described in Search conditions. Each column-name in the search condition, other than in a subquery, must name a column of the table or view. When the search condition includes a subquery in which the same table is the base object of both the UPDATE and the subquery, the subquery is completely evaluated before any rows are updated.

The search-condition is applied to each row of the table or view. The updated rows are those for which the results of the search-condition are true.

If the search-condition contains a subquery, the subquery can be thought of as being executed each time the search-condition is applied to a row, and the results of that subquery used in applying the search-condition. In actuality, a subquery with no correlated references may be executed only once. A subquery with a correlated reference may have to be executed once for each row.

CURRENT OF cursor-name
Identifies the cursor to be used in the update operation. The cursor-name must identify a declared cursor as explained in DECLARE CURSOR.

The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must not be read-only. For an explanation of read-only result tables, see DECLARE CURSOR.

When the UPDATE statement is executed, the cursor must be positioned on a row; that row is updated.

isolation-clause
Specifies the isolation level to be used for this statement.
WITH

Introduces the isolation level, which may be one of:

  • RR Repeatable read
  • RS Read stability
  • CS Cursor stability
  • UR Uncommitted read
  • NC No commit
If isolation-clause is not specified the default isolation is used. See isolation-clause for a description of how the default is determined.