Updating one element
To update a particular element in a collection, declare an update cursor for the collection host variable.
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:
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 Informix ESQL/C 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 Figure 1).
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;