Delete a collection element

After you select an individual element from a collection variable into an element variable, you can delete the element from the collection. For example, after you select a point from the collection variable vertexes with a collection query, you can remove the point from the collection.

The steps involved in deleting a collection element include:

Declare a collection variable and an element variable.
Select the collection from the database into the collection variable.
Declare a cursor so that you can select elements one at a time from the collection variable.
Write a loop or branch that locates the element that you want to delete.
Delete the element from the collection using a DELETE WHERE CURRENT OF statement that uses the collection variable as a collection-derived table.

The following figure shows a routine that deletes one of the four points in vertexes, so that the polygon becomes a triangle instead of a rectangle.

Figure 1: Routine that deletes one of the four points.

CREATE PROCEDURE shapes()

   DEFINE vertexes SET( point NOT NULL );
   DEFINE pnt point;

   SELECT definition INTO vertexes FROM polygons
      WHERE id = 207;

   FOREACH cursor1 FOR
      SELECT * INTO pnt FROM TABLE(vertexes)
      IF pnt = '(3,4)' THEN
            -- calls the equals function that
            -- compares two values of point type
         DELETE FROM TABLE(vertexes) 
            WHERE CURRENT OF cursor1;
         EXIT FOREACH;
      ELSE
         CONTINUE FOREACH;
      END IF;
   END FOREACH
. . .
END PROCEDURE;

In previous figure, the FOREACH statement declares a cursor. The SELECT statement is a collection-derived query that selects one element at a time from the collection variable vertexes into the element variable pnt.

The IF THEN ELSE structure tests the value currently in pnt to see if it is the point '(3,4)'. Note that the expression pnt = '(3,4)' calls the instance of the equal() function defined on the point data type. If the current value in pnt is '(3,4)', the DELETE statement deletes it, and the EXIT FOREACH statement exits the cursor.

Tip: Deleting an element from a collection stored in a collection variable does not delete it from the collection stored in the database. After you delete the element from a collection variable, you must update the collection stored in the database with the new collection. For an example that shows how to update a collection column, see Update the collection in the database.

The syntax for the DELETE statement is described in the HCL OneDB™ Guide to SQL: Syntax.