Replaces the values of the specified columns in all rows for which a specified 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.

The Vertica implementation of UPDATE differs from traditional databases. It does not delete data from disk storage; it writes two rows, one with new data and one marked for deletion. Rows marked for deletion remain available for historical queries.


UPDATE [ /*+LABEL (label‑string)*/ ]  [[database.]schema.]table-reference [AS] alias 
    SET set‑expression [,...]
    [ FROM from-list ]
    [ where‑clause ]



Assigns a label to a statement to identify it for profiling and debugging.


Database and schema. The default schema is public. If you specify a database, it must be the current database.


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.


A temporary name used to reference the table.

SET set‑expression[,...]

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 } 


  • column-name is any column that does not have primary key or foreign key referential integrity constraints and is not of a complex type. Native arrays are permitted.
  • 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.


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.


Vertica follows the SQL-92 transaction model, so successive INSERT, UPDATE, and DELETE statements are included in the same transaction. You do not need to explicitly start this transaction; however, you must explicitly end it with COMMIT, or implicitly end it with COPY; otherwise Vertica discards all changes that were made within the transaction.


  • The table you specify in the UPDATE list cannot also appear in the FROM list (no self joins); for example, the following 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.

  • You cannot update columns of complex types except for native arrays.

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


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)

You cannot use UPDATE to update individual elements of native arrays. Instead, replace the entire array value. The following example uses ARRAY_CAT to add an element to an array column:

=> SELECT * FROM singers;
 lname  | fname |                    bands
 Cher   |       | ["Sonny and Cher"]
 Jagger | Mick  | ["Rolling Stones"]
 Slick  | Grace | ["Jefferson Airplane","Jefferson Starship"]
(3 rows)
=> UPDATE singers SET bands=ARRAY_CAT(bands,ARRAY['something new']) 
	WHERE lname='Cher';
(1 row)

=> SELECT * FROM singers;
 lname  | fname |                    bands
 Jagger | Mick  | ["Rolling Stones"]
 Slick  | Grace | ["Jefferson Airplane","Jefferson Starship"]
 Cher   |       | ["Sonny and Cher","something new"]
(3 rows)