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.

For databases created in Vertica 9.3 and later, UPDATE writes directly to ROS. Databases created in earlier versions use a default load of AUTO.

Syntax

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

Parameters

/*+ hint[, hint] */

One or both of the following hints:

For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

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.

from‑list must not include the target table.

Privileges

Table owner or user with GRANT OPTION is grantor.

  • UPDATE privilege on table
  • USAGE privilege on schema that contains the table
  • SELECT privilege on the table when executing an UPDATE statement that references table column values in a WHERE or SET clause

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

  • UPDATE returns an error if both of the following conditions are true:
  • The table you specify in the UPDATE list cannot also appear in the FROM list (no self joins); for example, the following UPDATE statement is not allowed:

    => BEGIN;
    => UPDATE result_table
       SET address='new' || r2.address
       FROM result_table r2
       WHERE r2.cust_id = result_table.cust_id + 10;
       ERROR:  Self joins in UPDATE statements are not allowed
       DETAIL:  Target relation result_table also appears in the FROM list
    
  • If the joins specified in the WHERE predicate produce more than one copy of the row in the table to be updated, the new value of the row in the table is chosen arbitrarily.
  • If any primary key, unique key, or check constraints are enabled for automatic enforcement, Vertica enforces those constraints when you insert values into a table. If a violation occurs, Vertica rolls back the SQL statement and returns an error. This behavior occurs for INSERT, UPDATE, COPY, and MERGE SQL statements.

    Automatic constraint enforcement requires that you have SELECT privileges on the table containing the constraint.

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)