Updating Distinct UDT Columns - 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

Updating Distinct UDT Columns

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 SQL Data Definition Language).
  • 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.