Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: TutorialThe HCL OneDB™ Guide to SQL: Tutorial shows how to use basic and advanced structured query language (SQL) to access and manipulate the data in your databases. It discusses the data manipulation language (DML) statements as well as triggers and stored procedure language (SPL) routines, which DML statements often use.
Create and use SPL routines
Handle collections
Update the entire collection

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.