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. |