To update a distinct type column, either of the following must be true:
- The updated value must be of the same distinct type.
- There must exist a cast that converts the type of the updated value to the distinct type of the column and the cast is defined with the AS ASSIGNMENT option (see CREATE CAST in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).
By default, a distinct type has a system-generated cast of this type.
For example, suppose you have the following table definition:
CREATE TABLE table_1 ( column1 euro, column2 INTEGER) UNIQUE PRIMARY INDEX(column2);
Then the information in the following table is true:
| Example | Comment |
|---|---|
UPDATE table_1 SET column1 = 4.56 WHERE column2 = 5; |
Valid if there is a cast defined with the AS ASSIGNMENT option, either system-generated or user-defined, that converts DECIMAL to euro. |
UPDATE table_1 SET column1 = CAST(4.56 AS euro) WHERE column2 = 5; |
Valid if there is a cast defined with or without the AS ASSIGNMENT option, either system-generated or user-defined, that converts DECIMAL to euro. |
USING (price decimal(6,2)) UPDATE table_1 SET column1 = (CAST (:price AS euro)).roundup(0); |
Valid if the roundup() method returns the euro type and if there is an appropriate cast definition, either system-defined or user-defined, that converts DECIMAL to euro. Because an explicit cast operation is used, the UDT cast need not have been defined using the AS ASSIGNMENT option.
|
UPDATE table_1 SET column1 = column1.roundup(0); |
Valid if the roundup() method returns the euro type. |
UPDATE table_1 SET column1 = column3; |
Valid column reference. |
UPDATE table_1 SET column1 = NULL WHERE column2 = 10; |
Valid setting of a distinct column to NULL. |