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.