Updating one element

To update a particular element in a collection, declare an update cursor for the collection host variable.

About this task

An update cursor for a collection variable is a select cursor that was declared with the FOR UPDATE keywords. The update cursor allows you to sequentially scroll through the elements of the collection and update the current element with the UPDATE...WHERE CURRENT OF statement.

To update elements, follow these steps:

Procedure

Create a client collection variable in your program.
For more information, see Declaring collection variables and Manage memory for collections.
Declare the update cursor for the collection variable with the DECLARE statement and the FOR UPDATE clause; open this cursor with the OPEN statement.
By default, a select cursor on a collection variable supports updates. For more information about how to declare a select cursor, see Declare a select cursor for a collection variable.
Fetch the element or elements from the collection variable with the FETCH statement and the INTO clause.
For more information, see Selecting more than one element.
Update the fetched data with the UPDATE statement and the WHERE CURRENT OF clause.
Save the modified collection variable in the collection column.
For more information, see Operate on a collection column.
Close the update cursor with the CLOSE statement, and if you no longer need the cursor, free it with the FREE statement.

Results

The application must position the update cursor on the element to be updated and then use UPDATE...WHERE CURRENT OF to update this value.

The program in the following figure uses an update cursor to update an element in the collection variable, a_set, and then to update the set_col column of the tab_set table (see Sample tables with collection columns).

Figure 1: Updating one element in a collection host variable

EXEC SQL BEGIN DECLARE SECTION;
   int an_element;
   client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_set;
EXEC SQL select set_col into :a_set from tab_set
   where id_col = 6;

EXEC SQL declare set_curs cursor for
   select * from table(:a_set)
   for update;

EXEC SQL open set_curs;
while (SQLCODE != SQLNOTFOUND)
   {
   EXEC SQL fetch set_curs into :an_element;
   if (an_element = 4)
      {
      EXEC SQL update table(:a_set)(x)
         set x = 10
         where current of set_curs;
      break;
      }
   }

EXEC SQL close set_curs;

EXEC SQL update tab_set set set_col = :a_set
   where id_col = 6;

EXEC SQL deallocate collection :a_set;
EXEC SQL free set_curs;