Update all elements

You cannot include a WHERE clause on an UPDATE statement with a collection-derived table clause. Therefore, an UPDATE statement on a collection variable sets all elements in the collection to the value you specify in the SET clause. No update cursor is required to update all elements of a collection.

EXEC SQL BEGIN DECLARE SECTION;
   client collection list(smallint not null) a_list;
   int an_int;
EXEC SQL END DECLARE SECTION;
;

EXEC SQL update table(:a_list) (list_elmt)
      set list_elmt = 16;

In this example, the derived column list_elmt provides an alias to identify an element of the collection in the SET clause.