17.05 - Updating Structured UDTs Using a Mutator SET Clause - 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)

Mutator SET clauses provide a syntax for updating structured type columns. A mutator SET clause can only be used to update structured UDT columns (the specified column_name in a mutator SET clause must identify a structured UDT column). Each mutator method name you specify must be a valid mutator method name for the respective structured type value.

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.

There is one additional restriction on mutator SET clauses.

Consider the following example:

     SET mycol.R = x,
         mycol.y = mycol.R() + 3

As implemented by Vantage, any column references in an expression refer to the value of the column in the row before the row is updated. The system converts the two example clauses to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R() + 3)

This is a deviation from the ANSI SQL:2011 standard.

According to the ANSI SQL:2011 standard, the column reference to mycol in the second example equality expression of the mutator SET clause should reflect the change made to it from the first equality expression of the mutator SET clause, the assignment of x.

The two equality expressions are converted to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R(x).R() + 3)