UPDATE Syntax | SQL Statements | Teradata Vantage - 17.05 - UPDATE Syntax (Joined Tables Form) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
{ UPDATE | UPD }
  [ with_isolated_loading ]
  { table_name_1 | correlation_name_1 }
  [ FROM from_table_spec [,...] ]
  SET set_spec [,...]
  [ WHERE condition | ALL ] [;]

Syntax Elements

with_isolated_loading
WITH [NO] [CONCURRENT] ISOLATED LOADING
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_1
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
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.
from_table_spec
table_name_2 [ [AS] correlation_name ]
set_spec
column_name [.mutator_method_name] = expression
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.
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.
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.
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.
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_2
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.