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.
Eliminates rows from the result table that do not satisfy one or more predicates.
Syntax
WHERE boolean-expression
Semantics
boolean-expression
|
is an expression that returns true or false. Only rows for which the expression is true become part of the result set.
|
The boolean-expression can include 
Boolean operators and the following elements:
Syntax
[ AND | OR | NOT ]
Semantics
SQL uses a three-valued Boolean logic where the null value represents "unknown."
a
|
b
|
a AND b
|
a OR b
|
TRUE
|
TRUE
|
TRUE
|
TRUE
|
TRUE
|
FALSE
|
FALSE
|
TRUE
|
TRUE
|
NULL
|
NULL
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
FALSE
|
FALSE
|
NULL
|
FALSE
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
a
|
NOT a
|
TRUE
|
FALSE
|
FALSE
|
TRUE
|
NULL
|
NULL
|
Notes
- The operators
ANDOR - Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
BETWEEN-predicate
The special BETWEEN
Syntax
a BETWEEN x AND y
Semantics
a BETWEEN x AND y is equivalent to
a >= x AND a <= y
Similarly,
a NOT BETWEEN x AND y
is equivalent to
a < x OR a > y
Boolean-predicate
The Boolean predicate retrieves rows where the value of an expression is true, false, or unknown (null).
Syntax
expression IS [NOT] TRUE
expression IS [NOT] FALSE
expression IS [NOT] UNKNOWN
Semantics
A Boolean predicate always return true or false, never a null value, even when the operand is null. A null input is treated as the value UNKNOWN.
Notes
- Do not confuse the boolean-predicate with Boolean Operators or the Boolean data type, which can have only two values: true and false.
Syntax
[ AND | OR | NOT ]
Semantics
SQL uses a three-valued Boolean logic where the null value represents "unknown."
a
|
b
|
a AND b
|
a OR b
|
TRUE
|
TRUE
|
TRUE
|
TRUE
|
TRUE
|
FALSE
|
FALSE
|
TRUE
|
TRUE
|
NULL
|
NULL
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
FALSE
|
FALSE
|
NULL
|
FALSE
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
a
|
NOT a
|
TRUE
|
FALSE
|
FALSE
|
TRUE
|
NULL
|
NULL
|
Notes
- The operators
ANDOR - Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
IS UNKNOWNIS NOT UNKNOWN
column-value-predicate
Syntax
column-name comparison-op constant-expression
Semantics
column-name
|
is a single column of one the tables specified in the FROM clause.
|
comparison-op
|
is one of the comparison operators.
Comparison operators are available for all data types where comparison makes sense. All comparison operators are binary operators that return values of True, False, or NULL.
Syntax and Semantics
<
|
less than
|
>
|
greater than
|
<=
|
less than or equal to
|
>=
|
greater than or equal to
|
=
|
equal
|
<> or !=
|
not equal
|
Notes
The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.
- The comparison operators return NULL (signifying "unknown") when either operand is null.
|
constant-expression
|
is a constant value of the same data type as the column-name
|
Notes
Examples
Dimension.column1 = 2
Dimension.column2 = 'Seafood'
IN-predicate
Syntax
column-expression [ NOT ] IN ( list-expression )
Semantics
column-expression
|
is a single column of one the tables specified in the FROM clause.
|
list-expression
|
is a comma-separated list of constant values matching the data type of the column-expression
|
Examples
x IN (5, 6, 7)
|
|
x in (select a from table)
|
not allowed
|
(x, y) in ((5,6), (7,8))
|
not allowed
|
join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
LIKE-predicate
The LIKE predicate retrieves rows where the string value of a column matches a specified pattern. The pattern can contain one or more wildcard characters, which match all valid characters. ILIKE is equivalent to LIKE except that the match is case-insensitive (non-standard extension).
Syntax
string { LIKE | ILIKE } pattern [ESCAPE escape-character]
string NOT { LIKE | ILIKE } pattern [ESCAPE escape-character]
Semantics
string
|
(CHAR or VARCHAR) is the column value to be compared to the pattern.
|
NOT
|
returns true if LIKE returns false, and vice versa (equivalent to NOT string LIKE pattern).
|
pattern
|
specifies a string containing wildcard characters.
- underscore (_) matches any single character.
- percent sign (%) matches any string of zero or more characters.
|
ESCAPE
|
specifies an escape-character. A null escape character ('') disables the escape mechanism. To match the escape character itself, use two consecutive escape characters.
|
escape-character
|
when preceding an underscore or percent sign character in the pattern, causes that character to be treated as a literal rather than a wildcard. The default escape character is the backslash (/) character.
|
Notes
- LIKE requires the entire string expression to match the pattern. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.
- The LIKE predicate does not ignore trailing "white space" characters. If the data values that you want to match have unknown numbers of trailing spaces, tabs, etc., terminate each LIKE predicate pattern with the percent sign wildcard character.
- To use a backslash character as a literal, specify a different escape character and use two backslashes. For example, if the escape character is circumflex (^), you would use ^\\ to specify a literal backslash. (Alternative: simply use four backslashes.)
- The use of a column data type other than character or character varying (implicit string conversion) is not supported and not recommended.
- Error messages caused by the LIKE predicate may refer to it by the following symbols instead of the actual keywords:
~~
|
LIKE
|
~~*
|
ILIKE
|
!~~
|
NOT LIKE
|
!~~*
|
NOT ILIKE
|
Examples
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false
NULL-predicate
Syntax
column-name IS [ NOT ] NULL
Semantics
column-name
|
is a single column of one the tables specified in the FROM clause.
|
Examples
See Also
NULL Value
Notes
table
|
specifies the name of a table in the schema. You cannot UPDATE a projection.
|
column
|
specifies the name of a non-key column in the table.
|
expression
|
specifies a value to assign to the column. The expression can use the current values of this and other columns in the table. For example:
UPDATE T1 SET C1 = C1+1;
|
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. Note that the target table must not appear in the fromlist.
Specifies one or more source tables from which to retrieve rows.
Syntax
FROM table-reference [ , table-reference ] ...
Syntax
table-primary | joined-table
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Syntax
{ table-name [ AS ] alias
[ ( column-alias [ , ...] ) ] [ , ...] ]
| ( joined-table ) }
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Semantics
table-name
|
specifies a table in the logical schema. Vertica selects a suitable projection to use.
|
alias
|
specifies a temporary name to be used for references to the table.
|
column-alias
|
specifies a temporary name to be used for references to the column.
|
joined-table
|
specifies an outer join.
|
Semantics
table-primary
|
specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
|
joined-table
|
specifies an outer join.
|
Syntax
table-primary | joined-table
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Syntax
{ table-name [ AS ] alias
[ ( column-alias [ , ...] ) ] [ , ...] ]
| ( joined-table ) }
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Semantics
table-name
|
specifies a table in the logical schema. Vertica selects a suitable projection to use.
|
alias
|
specifies a temporary name to be used for references to the table.
|
column-alias
|
specifies a temporary name to be used for references to the column.
|
joined-table
|
specifies an outer join.
|
Semantics
table-primary
|
specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
|
joined-table
|
specifies an outer join.
|
Semantics
table-reference
|
is a table-primary or a joined-table.
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Syntax
{ table-name [ AS ] alias
[ ( column-alias [ , ...] ) ] [ , ...] ]
| ( joined-table ) }
Syntax
table-reference join-type table-reference
ON join-predicate
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
Semantics
table-reference
|
is a table-primary or another joined-table.
|
join-type
|
is one of the following:
INNER JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
|
join-predicate
|
for an INNER JOIN, an equi-join based on a PRIMARY KEY-FOREIGN KEY constraint relationship between the joined tables.
for an OUTER JOIN: an equi-join based on a PRIMARY KEY-join key relationship between the joined tables that does not involve a FOREIGN KEY constraint.
|
Notes
Semantics
table-name
|
specifies a table in the logical schema. Vertica selects a suitable projection to use.
|
alias
|
specifies a temporary name to be used for references to the table.
|
column-alias
|
specifies a temporary name to be used for references to the column.
|
joined-table
|
specifies an outer join.
|
|
Notes
- If more than one table-reference is specified in the FROM list, they are joined together, in which case the first table in the list is the anchor table. The anchor table can be:
- the fact table in a star schema
- the central fact table in a snowflake schema
- a dimension table in a snowflake schema that functions as a fact table (with a restriction as described in the Snowflake Schema section of the Database Administrator's Guide).
- Full Cartesian products (joins with no join-predicate) are not allowed.
Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.
Syntax
column-reference = column-reference
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Syntax
[ tablename. ] columnname
Semantics
tablename
|
is one of:
- the name of a table
- an alias for a table defined by means of a FROM clause in a query
|
columnname
|
is the name of a column that must be unique across all the tables being used in a query
|
Notes
- There are no space characters in a column reference.
Semantics
column-reference
|
refers to a column of one the tables specified in the FROM clause.
|
Notes
- Self-joins are not allowed.
|
