Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
UPDATE statementUse the UPDATE statement to change the values in one or more columns of one or more existing rows in a table or view.
SET ClauseUse the SET clause to identify the columns to be updated and assign values to each column.
Updating Collection Columns

Updating Collection Columns

You can use the SET clause to update values in a collection column. For more information, see Collection Constructors.

A collection variable can update a collection-type column. With a collection variable, you can insert one or more individual elements of a collection. For more information, see Collection-Derived Table.

For example, suppose you define the tab1 table as follows:

CREATE TABLE tab1 
(
   int1 INTEGER, 
   list1 LIST(ROW(a INTEGER, b CHAR(5)) NOT NULL),
   dec1 DECIMAL(5,2)
);

The following UPDATE statement updates a row in tab1:

UPDATE tab1 
   SET list1 = LIST{ROW(2, 'zyxwv'),
      ROW(POW(2,6), ‘=64'), 
      ROW(ROUND(ROOT(146)), ‘=12')},
   WHERE int1 = 10;

Collection column list1 in this example has three elements. Each element is an unnamed ROW type with an INTEGER field and a CHAR(5) field. The first element includes two literal values: an integer ( 2 ) and a quoted string ('zyxwv').

The second and third elements also use a quoted string to indicate the value for the second field. They each designate the value for the first field with an expression, however, rather than with a literal value.