UPDATE

Replaces the values of the specified columns in all rows for which a specific condition is true. All other columns and rows in the table are unchanged. If successful, UPDATE returns the number of rows updated. A count of 0 indicates no rows matched the condition.

UPDATE inserts new records into the WOS and marks the old records for deletion. If the WOS fills up, the operation overflows to the ROS.

Syntax

UPDATE [ /*+ hint[, hint] */ ]  [schema.]table-reference [AS] alias 
... SET set‑expression [, ... ]
... [ FROM from-list ]
... [ where‑clause ]

Parameters

/*+ hint[, hint] */

One or both of the following hints:

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
table‑reference

Specifies a table, one of the following:

  • An optionally qualified table name with optional table aliases, column aliases, and outer joins.
  • An outer join table.

You cannot update a projection.

alias

A temporary name used to reference the table.

SET set‑expression[,...]

Specifies the columns to update. Each set‑expression in the SET clause specifies a target column and its new value as follows:

column-name =  { expression | DEFAULT } 

where:

  • column-name is any column that does not have primary key or foreign key referential integrity constraints.
  • expression specifies a value to assign to the column. The expression can use the current values of this and other table columns. For example:
    UPDATE T1 SET C1 = C1+1

UPDATE only modifies the columns specified by the SET clause. Unspecified columns remain unchanged.

FROM from‑list

A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the UPDATE expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT command.

Important: from‑list must not include the target table.

Privileges

Table owner or user with GRANT OPTION is grantor.

Subqueries and Joins

UPDATE supports subqueries and joins, which is useful for updating values in a table based on values that are stored in other tables. For details, see Subqueries in UPDATE and DELETE Statements in Analyzing Data.

Restrictions

Examples

In the fact table, modify the price column value for all rows where the cost column value is greater than 100:

=> UPDATE fact SET price = price - cost * 80 WHERE cost > 100;

In the retail.customer table, set the state column to NH when the CID column value is greater than 100:

=> UPDATE retail.customer SET state = 'NH' WHERE CID > 100;

To use table aliases in UPDATE queries, consider the following two tables:

=> SELECT * FROM result_table;
 cust_id |      address
---------+--------------------
      20 | Lincoln Street
      30 | Beach Avenue
      30 | Booth Hill Road
      40 | Mt. Vernon Street
      50 | Hillside Avenue
(5 rows)
=> SELECT * FROM new_addresses;
 new_cust_id | new_address
-------------+---------------
          20 | Infinite Loop
          30 | Loop Infinite
          60 | New Addresses
(3 rows)

The following query and subquery use table aliases to update the address column in result_table (alias r) with the new address from the corresponding column in the new_addresses table (alias n):

=> UPDATE result_table r
   SET address=n.new_address
   FROM new_addresses n
   WHERE r.cust_id = n.new_cust_id;

result_table shows the address field updates made for customer IDs 20 and 30:

=> SELECT * FROM result_table ORDER BY cust_id;
 cust_id |     address
---------+------------------
      20 | Infinite Loop
      30 | Loop Infinite
      30 | Loop Infinite
      40 | Mt. Vernon Street
      50 | Hillside Avenue
(5 rows)