UPDATE

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.

Syntax

UPDATE [[database.]schema.]table-reference [AS] alias 
    SET set-expression [,...]
    [ FROM from-list ]
    [ where-clause ]

Parameters

[database.]schema

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

table-reference

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

The columns to update from one or more set expressions. Each SET clause expression 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 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
  

• DEFAULT sets column-name to its default value, or is ignored if no default value is defined for this column.

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.

The FROM clause can reference the target table as follows:

FROM DEFAULT [join-type] JOIN table-reference [ ON join-predicate ]

DEFAULT specifies the table to update. This keyword can be used only once in the FROM clause, and it cannot be used elsewhere in the UPDATE statement.

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.

Committing successive table changes

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.

Restrictions

• You cannot update an immutable table.

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

• If the joins specified in the FROM clause or WHERE predicate produce more than one copy of the row in the target table, the new value of the row in the table is chosen arbitrarily.

• If primary key, unique key, or check constraints are enabled for automatic enforcement in the target table, Vertica enforces those constraints when you load new data. If a violation occurs, Vertica rolls back the operation and returns an error.

• If an update would violate a table or schema disk quota, the operation fails. For more information, see Disk quotas.

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)

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';
 OUTPUT
--------
      1
(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)