SET Clause - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

SET Clause

 

Syntax Element …

Specifies …

SET

the 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” on page 481).

column_name

the 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

the 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” on page 481.

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” on page 101.

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

the 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” on page 124 and “Rules for Using Scalar Subqueries in UPDATE Requests” on page 482 for details.

ALL

Indicates that all rows in the specified table are to be updated.

The ALL option is a Teradata extension to ANSI SQL.