Example of Updating a Collection

Suppose that the set_col column of a table called table1 is defined as a SET and that it contains the values {1,8,4,5,2}. The following program changes the element whose value is 4 to a value of 10:

main
{
   EXEC SQL BEGIN DECLARE SECTION;
      int a;
      collection b;
   EXEC SQL END DECLARE SECTION;

   EXEC SQL allocate collection :b;
   EXEC SQL select set_col into :b from table1
      where int_col = 6;

   EXEC SQL declare set_curs cursor for
      select * from table(:b) for update;
   EXEC SQL open set_curs;
   while (SQLCODE != SQLNOTFOUND)
   {
      EXEC SQL fetch set_curs into :a;
      if (a = 4)
      {
         EXEC SQL update table(:b)(x)
            set x = 10 where current of set_curs;
         break;
      }
   }
   EXEC SQL update table1 set set_col = :b
      where int_col = 6;
   EXEC SQL deallocate collection :b;
   EXEC SQL close set_curs;
   EXEC SQL free set_curs;
}

After you execute this program, the set_col column in table1 contains the values {1,8,10,5,2}.

This program defines two collection variables, a and b, and selects a SET from table1 into b. The WHERE clause ensures that only one row is returned. Then the program defines a Collection cursor, which selects elements one at a time from b into a. When the program locates the element with the value 4, the first UPDATE statement changes that element value to 10 and exits the loop.

In the first UPDATE statement, x is a derived-column name used to update the current element in the collection-derived table. The second UPDATE statement updates the base table table1 with the new collection.

For information on how to use collection host variables in programs, see the discussion of complex data types in the HCL OneDB™ ESQL/C Programmer's Manual.