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 you can include for readability.

Target Table Options

table_name_1
Name of the base table, queue table, or derived table to be updated, or the name of a view through which the table is accessed.
If you specify a correlation name for table_name_1 in the FROM clause, then you must specify that correlation name for the updated table instead of table_name_1.
See Example: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause.
correlation_name
An alias for table_name_1.
You cannot specify a correlation name for the table if the UPDATE statement includes the FROM clause.
Correlation names are also referred to as range variables.
The correlation_name option is a Teradata extension to the ANSI SQL:2011 standard.

FROM Clause

FROM
Keyword introducing a table list of the updated table and any other tables from which field values are taken to update the updated table.
When you use an UPDATE syntax that requires a FROM clause, you should specify the names of all outer tables in the clause.
The UPDATE statement FROM clause is a Teradata extension to the ANSI SQL:2011 standard.
table_name_2
Name of one or more base tables, queue tables, derived tables, or views.
table_name_1 must be a member of the table_name_2 table list.
If you do not specify a correlation name for a table_name_2 list object, or if you define a correlation name for it instead of table_name_1, then table_name_2 cannot be a derived table.
If any table_name_2 list member is a view, then that view must be updatable.
If you do not specify a database name, the system assumes the current database.
correlation_name
Alias for a member of the table_name_2 table list. Correlation names are also referred to as range variables.
A correlation name must be specified for at least one of the tables in a self-join operation.
If you specify a correlation name for table_name_1 in the table_name_2 table list using the joined tables syntax, you must specify that correlation name instead of the true name for table_name_1. See Example: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause.

SET Clause

SET
Names of one or more columns whose data is to be updated, and the expressions that are used for update.
If you are updating a UDT column, then you must use the mutator SET clause syntax. See Updating Structured UDTs Using a Mutator SET Clause.
column_name
Name of a column whose value is to be set to the value of the specified expression.
The column_name field is for a column name only.
Do not use fully-qualified column name forms such as databasename.tablename.columnname or tablename.columnname.
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.
A mutator method name is the same name as the attribute name that it modifies. Within the mutated set clause, parentheses following the attribute name are not valid.
To update a structured UDT column, you must use the mutator SET clause syntax. See Updating Structured UDTs Using a Mutator SET Clause.
expression
An expression that produces the value for which column_name is to be updated.
expression can include constants, nulls (specified by the reserved word NULL), a DEFAULT function, or an arithmetic expression for calculating the new value. Values in a targeted row before the update can be referenced in an expression.
You can specify a scalar UDF for expression if it returns a value expression.
For join updates, you can reference columns in expression from rows participating in the join.
When host variables are used in the SET clause, they must always be preceded by a COLON character.
WHERE
A conditional clause. For more information see WHERE Clause.
You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression.
If you specify a WHERE clause, you must have SELECT access on the searched objects.
search_condition
Conditional expression to be used for determining rows whose values are to be updated. The condition can reference multiple tables or specify a scalar subquery. See Scalar Subqueries and Rules for Using Scalar Subqueries in UPDATE Requests.
ALL
Indicates that all rows in the specified table are to be updated.
The ALL option is a Teradata extension to ANSI SQL.