UPDATE Syntax | SQL Statements | Teradata Vantage - UPDATE Syntax (Basic Form, FROM Clause) - Analytics Database - Teradata Vantage

{ UPDATE | UPD }
  [ with_isolated_loading ]
  { table_name_1 | correlation_name_1 }
  FROM table_name_2 [ [AS] correlation_name_2 ]
  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.

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.

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.