Delete one element

To delete 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 delete the current element with the DELETE...WHERE CURRENT OF statement.

To delete particular elements, follow the same steps for how to update particular elements (see Updating one element). In these steps, you replace the use of the UPDATE...WHERE CURRENT OF statement with the DELETE...WHERE CURRENT OF statement.

The application must position the update cursor on the element to be deleted and then use DELETE...WHERE CURRENT OF to delete this value. The following code fragment uses an update cursor and a DELETE statement with a WHERE CURRENT OF clause to delete the element from the set_col column of tab_set (see Sample tables with collection columns).

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

EXEC SQL allocate collection :a_set;
EXEC SQL select set_col, cardinality(set_col)
   into :a_set, :set_size 
   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 (i < set_size)
{
   EXEC SQL fetch set_curs into :an_int;
   if (an_int == 4)
   {
      EXEC SQL delete from table(:a_set) 
         where current of set_curs;
      break;
   }
   i++;
}
EXEC SQL close set_curs;
EXEC SQL free set_curs;

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

EXEC SQL deallocate collection :a_set;

Suppose that in the row with an id_col value of 6, the set_col column contains the values {1,8,4,5,2} before this code fragment executes. After the DELETE...WHERE CURRENT OF statement, this collection variable contains the elements {1,8,5,2}. The UPDATE statement at the end of this code fragment saves the modified collection into the set_col column of the database. Without this UPDATE statement, the collection column never has element 4 deleted.