Update collection types

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.

Important: Do not confuse the SET keyword of an UPDATE statement with the SET constructor that indicates that a collection is a SET data type.

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 programs and SPL routines to update elements within a collection. For information about how to create programs to update a collection, see the HCL OneDB™ ESQL/C Programmer's Manual. For information about how to create SPL routines to update a collection, see the section Handle collections.