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

To update a structured type column, the updated value must be of the same structured type. For example, suppose you have the following table definition:

     CREATE TABLE table_1 (
       column_1 address, 
       column_2 INTEGER
       column_3 address)
     UNIQUE PRIMARY INDEX(column2);

Then the information in the following table is true:

 

Example

Comment

UPDATE table_1
  SET column_1 = 
  '17095 Via Del Campo;92127';	

Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address.

USING (addr varchar(30), c2 INTEGER)
UPDATE table_1
  SET column_1 = :addr
WHERE column_2 = :c2;	

Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address.

UPDATE table_1 
  SET column_1 = 
  NEW address('17095 Via Del Campo',
              '92127');

Valid invocation of a constructor method.

USING (street VARCHAR(20), zip   CHARACTER(5))
UPDATE table_1 
  SET column_1 = NEW   address(:street,:zip);

Valid invocation of a constructor method with host variables.

UPDATE table_1 
  SET column_1 = NEW address();

Valid invocation of the constructor function with the NEW syntax.

UPDATE table_1 
  SET column_1 = 
  (NEW address().street('17087 Via
  Del Campo').zip('92127'));

Valid mutator invocations:

1 The constructor function is invoked. The result is an address value whose attribute values are set to their defaults.

2 The mutator for the street attribute is invoked. The result is an updated address value with its street attribute modified.

3 The mutator for the zip attribute is invoked. The result is another updated address value with its zip attribute modified. This result also contains the change to the street attribute.

4 The result address value is used to update column_1 of table_1.

UPDATE table_1
  SET column_1 =
  column1.zip('92128');

Valid invocation of a mutator to update the zip attribute.

UPDATE table_1 
  SET column_1 = 
  (CAST ('17095 Via Del Campo;92127'   AS address) );   	

Valid if the cast from VARCHAR to structured type address is valid.

UPDATE table_1
  SET column_1 = column3;

Valid column reference.

UPDATE table_1 
  SET column_1 = NULL;

Valid setting of a structured type column to NULL.