Operate on a collection column

The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. You must use an INSERT or UPDATE statement to explicitly save the contents of the collection variable into the collection column.

You can use the SELECT, UPDATE, INSERT, and DELETE statements to access a collection column (SET, MULTISET, or LIST), as follows:

The SELECT statement fetches all elements from a collection column.
The INSERT statement inserts a new collection into a collection column.
Use the INSERT statement on a table or view name and specify the collection variable in the VALUES clause.

Figure 1 shows an INSERT statement that saves the contents of a collection variable in a collection column.
The UPDATE statement updates the entire collection in a collection column with new values.
Use an UPDATE statement on a table or view name and specify the collection variable in the SET clause.

Figure 1 shows an UPDATE statement that saves the contents of a collection variable in a collection column.

For more information about how to use these statements with collection columns, see the HCL Informix® Guide to SQL: Tutorial.