UPDATE command

This command changes the data in a table.

Syntax

{UPDATE table_name (correlation_name)
   SET column_name = {expression | NULL}
      (column_name = {expression | NULL} ) ...
   (WHERE search_condition)

Keyword	Description
`table_name`	The name of an existing table that you can access. May include the owner's name if it is not you, for example, "owner.table"
`correlation_name`	Also called an alias. Used to relabel the name of the reference in other clauses in the statement
`column_name`	A column within the table. Parentheses are required only if the column list contains more than one column
`expression`	The operation or function to execute on the specified `column_name`
`search_condition`	A valid condition that evaluates to TRUE, FALSE, or UNKNOWN

Use

This statement changes one or more column values in an existing row of a table. The table may be a base table or view. You can set any number of columns to values and follow the whole column_name = value_expression clause with a comma if there is another such to follow. As an alternative to an explicit value, you can set the column to NULL or to the DEFAULT defined for the column.

You can use value_expression to refer to the current values in the table being updated. Any such references refer to the values of all of the columns before any of them were updated. This allows you to do such things as double all column values (if numeric) by specifying:

column_name = column_name * 2

You can also swap values between columns. Value expressions can also use subqueries.

The UPDATE is applied to all rows that fulfill the WHERE clause, which is one of two types. The WHERE predicate form is like the WHERE predicate clause in the SELECT statement: it uses an expression that can be TRUE, FALSE or UNKNOWN for each row of the table to be updated, and the UPDATE is performed wherever it is TRUE.

Be careful of omitting the WHERE clause; if you do, the UPDATE is performed on every row in the table. You can use the WHERE CURRENT OF form in static or dynamic SQL if the cursor direction is updatable (in other words, not through views) and provided the target table is open and positioned on a row. The UPDATE is then applied to the row on which it is positioned. When using WHERE CURRENT OF in dynamic SQL, you can omit the table name from the UPDATE clause, because the table in the cursor is implied.

In either case, for the UPDATE to be successful, the following conditions must be met:

The statement issuer must have the UPDATE privilege on each column of the table being set
If the target table is a view, it must be updatable
If the current transaction is read-only, the target table must be temporary
If the UPDATE is performed through a cursor that specifies ORDER BY, it may not set the values of any columns specified in the ORDER BY clause
If the target table is a view, the "value_expression" in the SET clause must not directly, or through views, reference its leaf-underlying table (the base table where the data ultimately resides)
The "value_expression" may not use aggregate functions except in subqueries
Each column of the target table can be altered only once by the same UPDATE statement
If the UPDATE is on a cursor that specified FOR UPDATE, each column being set must have been specified or implied by that FOR UPDATE
If the UPDATE is made through a view, it may be constrained by a WITH CHECK OPTION clause

UPDATE command

Syntax

Use

Example