Updating Distinct UDT Columns - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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.