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 privilege USAGE 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 privilege UPDATE 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 optional FROM clause.

Syntax

update::=

Update Statement

Update Statement

Update Statement

Usage Notes

  • By using the FROM clause, you can define several tables which are joined through the WHERE clause. By that, you can specify complex update conditions which are similar to complex SELECT statements. The updated table must be specified from within the FROM clause.

    • 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.

  • If a column is set to the value DEFAULT, then the rows in this column affected by the UPDATE 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 value NULL 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.

Examples

--Salary increase by 10%
UPDATE staff SET salary=salary*1.1 WHERE name='SMITH';
--Euro conversion
UPDATE staff AS U SET U.salary=U.salary/1.95583, U.currency='EUR'
       WHERE U.currency='DM';
--Complex UPDATE using a join with another table
UPDATE staff AS U
SET U.salary=V.salary, U.currency=V.currency
FROM staff AS U, staff_updates AS V
WHERE U.name=V.name;