Updating Collection Types (IDS)

When you use DB–Access to update a collection type, you must update the entire collection. The following statement shows how to update the projects column. To locate the row that needs to be updated, use the IN keyword to perform a search on the direct_reports column.

UPDATE manager
SET projects = "LIST 
{
   ROW('brazil_project', SET{'Pryor', 'Murphy', 'Kinsley',
      'Bryant'}), 
   ROW ('cuba_project', SET{'Forester', 'Barth', 'Lewis',
      'Leonard'})
}"
WHERE 'Williams' IN direct_reports

The first occurrence of the SET keyword in the preceding statement is part of the UPDATE statement syntax.

Although you can use the IN keyword to locate specific elements of a simple collection, you cannot update individual elements of a collection column from DB–Access. However, you can create ESQL/C programs and SPL routines to update elements within a collection. For information about how to create an ESQL/C program to update a collection, see the IBM Informix: ESQL/C Programmer's Manual. For information about how to create SPL routines to update a collection, see the section Handling Collections (IDS).