UPDATE
Purpose
Using the UPDATE
the statement, you can make targeted changes to the contents of the table.
Prerequisites
-
You need either the system privilege
USE ANY SCHEMA
or the object privilegeUSAGE
on the target schema, or the schema must be owned by you or one of your assigned roles. - You need either the system privilege
UPDATE ANY TABLE
or the object privilegeUPDATE
on the table or its schema, or the table must be owned by you or one of your assigned roles. - You need the appropriate
SELECT
privileges on the schema object referenced in the optionalFROM
clause.
Syntax
update::=
Usage Notes
- By using the
FROM
clause, you can define several tables which are joined through theWHERE
clause. By that, you can specify complex update conditions which are similar to complexSELECT
statements. The updated table must be specified from within theFROM
clause. - If a column is set to the value
DEFAULT
, then the rows in this column affected by theUPDATE
are filled automatically. For identity columns, a monotonically increasing number is generated, and for columns, with default values their respective default value is used. For all other columns, the valueNULL
is used. - For information on default values, refer to Default Values section. For information on identity columns, refer to the Default Values section.
- The
PREFERRING
clause defines a Skyline preference term. For more information, refer to the Skyline section.
Internally this statement is transformed into a MERGE statement. Therefore, error messages can refer to the MERGE command. If one row is updated multiple times, the new value must be identical. Otherwise, the error message "Unable to get a stable set of rows in the source tables" will be displayed.