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.