17.05 - Updating Distinct UDT Columns - 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)
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.
  1. Host variable :price is converted to euro.
  2. The roundup() method is invoked.
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.