Syntax Elements - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Isolated Loading Options

WITH ISOLATED LOADING
The UPDATE can be performed as a concurrent load isolated operation.
NO
The UPDATE is not performed as a concurrent load isolated operation.
CONCURRENT
Optional keyword that can be included for readability.

Target Table Options

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.

SET Clause

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.

WHERE Clause

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.

VALUES Clause

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.
DEFAULT
DEFAULT (column_name)
The DEFAULT function should update the column with its default value in the position by which it is called in the expression list.
If you specify DEFAULT (column_name), then the DEFAULT function updates the default value for the column specified by column_name, and the position of the DEFAULT call in the expression list is ignored.
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.

ANSI Compliance

The upsert form of UPDATE is a Teradata extension to the ANSI SQL:2011 standard.