Insert into and update a collection column

The INSERT and UPDATE statements support collection columns as follows:

• To insert a collection of elements into an empty collection column, specify the new elements in the VALUES clause of the INSERT statement.
• To update the entire collection in a collection column, specify the new elements in the SET clause of the UPDATE statement. The UPDATE statement must also specify a derived column name to create an identifier for the element. You then use this derived column name in the SET clause to identify where to assign the new element values.

In the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement, the element values can be in any of the following formats:

• The collection host variable
• A literal collection value

To represent literal values for a collection column, you specify a literal-collection value. You create a literal-collection value, introduce the value with the SET, MULTISET, or LIST keyword and provide the field values in a comma-separated list that is enclosed in braces. You surround the entire literal-collection value with quotes (double or single). The following INSERT statement inserts the literal collection of SET {7, 12, 59, 4} into the set_col column in the tab_set table (that Sample tables with collection columns defines):

EXEC SQL insert into tab_set values 
(
   5, 'set{7, 12, 59, 4}'
);

The UPDATE statement in the following figure overwrites the SET values that the previous INSERT added to the tab_set table.

EXEC SQL update tab_set 
   set set_col = ("list{1,2,3,4}")
   where id_col = 5;

If any character value appears in this literal-collection value, it too must be enclosed in quotes; this condition creates nested quotes. For example, for column col1 of type SET(CHAR(5), a literal value can be expressed as follows:

'SET{"abcde"}'

To specify nested quotes in an SQL statement in the program, you must escape every double quotation mark when it appears in a quotation mark string. The following INSERT statement shows how to use escape characters for inner double quotation marks:

EXEC SQL insert into (col1) tab1 
   values ('SET{\"abcde\"}');

When you embed a double-quoted string inside another double-quoted string, you do not need to escape the inner-most quotation marks, as the following INSERT statement shows:

EXEC SQL insert into tabx
   values (1, "set{""row(12345)""}");

If the collection or row type is nested, that is, if it contains another collection or row type as a member, the inner collection or row does not need to be enclosed in quotes. For example, for column col2 whose data type is LIST(ROW(a INTEGER, b SMALLINT) NOT NULL), you can express the literal value as follows:

'LIST{ROW(80, 3)}'