Upsert UPDATE Syntax | SQL Statements | Teradata Vantage - UPDATE Syntax (Upsert Form) - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
{ UPDATE | UPD }
  [ with_isolated_loading ]
  table_name_1
  [ [AS] correlation_name ]
  SET set_spec [,...]
  WHERE condition ELSE { INSERT | INS } [INTO] table_name_2
  { [ VALUES ] ( expression [,...] ) |
    ( column_name [,...] ) [ VALUES ] ( expression [,...] ) |
    DEFAULT VALUES
  } [;]

Syntax Elements

with_isolated_loading
WITH [NO] [CONCURRENT] ISOLATED LOADING
table_name_1
Name of the table or queue table in which the row set is to be updated, or the name of the view through which the base table is accessed. The table must have a primary index and can be row-partitioned but cannot be column partitioned.
This must match the specification for table_name_2.
correlation_name
An alias for table_name_1.
set_spec
column_name [.mutator_method_name] = expression
condition
The predicate that specifies the row to be updated:
  • If the UPDATE target is a SET table with a UPI, then only one row can be updated per request.
  • If the UPDATE target is a SET table with a NUPI or a MULTISET table, then multiple rows can be updated per request.
table_name_2
Name of the table or queue table in which the row set is to be inserted, or the name of the view through which the base table is accessed.
This name must match the name specified for table_name_1.
expression
The non-default set of values to be inserted into table_name_2.
You must specify values or nulls for each column defined for the table named table_name_2 or the insert fails.
column_name
Column set into which the values specified by expression are to be inserted.
DEFAULT VALUES
A row consisting of default values is to be added to table_name.
If any column does not have a defined DEFAULT phrase, then the process inserts a null for that column.
WITH [NO] ISOLATED LOADING
Without NO, the UPDATE can be performed as a concurrent load isolated operation.
With NO, the UPDATE is not performed as a concurrent load isolated operation.
CONCURRENT
Optional keyword that you can include for readability.
column_name = expression
Value expression to be updated in column column_name. This can be a DEFAULT function.
Host variables in the SET clause must always be preceded by a COLON character.
You cannot specify a derived period column name.
mutator_method_name
Name of a mutator method that is to perform some update operation on column_name.
See Updating Structured UDTs Using a Mutator SET Clause.