Updating Collection Columns (IDS)

You can use the SET clause to update values in a collection column. For more information, see Collection Constructors.

A collection variable can update a collection-type column. With a collection variable, you can insert one or more individual elements of a collection. For more information, see Collection-Derived Table.

For example, suppose you define the tab1 table as follows:

CREATE TABLE tab1 
(
   int1 INTEGER, 
   list1 LIST(ROW(a INTEGER, b CHAR(5)) NOT NULL),
   dec1 DECIMAL(5,2)
)

The following UPDATE statement updates a row in tab1:

UPDATE tab1 
   SET list1 = LIST{ROW(2, 'zyxwv'),
      ROW(POW(2,6), '=64'), 
      ROW(ROUND(ROOT(146)), '=12')},
   where int1 = 10

Collection column list1 in this example has three elements. Each element is an unnamed ROW type with an INTEGER field and a CHAR(5) field. The first element includes two literal values: an integer ( 2 ) and a quoted string ('zyxwv').

The second and third elements also use a quoted string to indicate the value for the second field. They each designate the value for the first field with an expression, however, rather than with a literal value.