Update the entire collection

If you want to update all the elements of a collection to the same value, or if the collection contains only one element, you do not need to use a cursor. The statements in the following figure show how you can retrieve the collection into a collection variable and then update it with one statement.

Figure 1. Retrieve and update the collection.

DEFINE s SET (INTEGER NOT NULL);

SELECT numbers INTO s FROM orders
   WHERE order_num = 10;

UPDATE TABLE(s)(x) SET x = 0;

UPDATE orders SET numbers = s
   WHERE order_num = 10;

The first UPDATE statement in this example uses a derived column named x with the collection-derived table s and gives all the elements in the collection the value 0. The second UPDATE statement stores the new collection in the database.