Updating a Row Variable (ESQL/C)

About this task

The UPDATE statement with the Collection-Derived Table segment allows you to update fields in a row variable. The Collection-Derived Table segment identifies the row variable in which to update the fields. For more information, see Collection-Derived Table.

Procedure

To update fields

Create a row variable in your program.
Optionally, select a ROW-type column into the row variable with the SELECT statement (without the Collection-Derived Table segment).
Update fields of the row variable with the UPDATE statement and the Collection-Derived Table segment.
After the row variable contains the correct fields, you then use the UPDATE or INSERT statement on a table or view name to save the row variable in the ROW column (named or unnamed).

Results

The UPDATE statement and the Collection-Derived Table segment allow you to update a field or a group of fields in the row variable. Specify the new field values in the SET clause. For example, the following UPDATE changes the x and y fields in the myrect row variable:

EXEC SQL BEGIN DECLARE SECTION;
   row (x int, y int, length float, width float) myrect;
EXEC SQL END DECLARE SECTION;
. . .
EXEC SQL select into :myrect from rectangles where area = 64;
EXEC SQL update table(:myrect) set x=3, y=4;

Suppose that after the SELECT statement, the myrect2 variable has the values x=0, y=0, length=8, and width=8. After the UPDATE statement, the myrect2 variable has field values of x=3, y=4, length=8, and width=8. You cannot use a row variable in the Collection-Derived Table segment of an INSERT statement.

You can, however, use the UPDATE statement and the Collection-Derived Table segment to insert new field values into a row host variable, if you specify a value for every field in the row.

For example, the following code fragment inserts new field values into the row variable myrect and then inserts this row variable into the database:

EXEC SQL update table(:myrect)
   set x=3, y=4, length=12, width=6;
EXEC SQL insert into rectangles
   values (72, :myrect);

If the row variable is an untyped variable, you must use a SELECT statement before the UPDATE so that can determine the data types of the fields. An UPDATE of fields in a row variable cannot include a WHERE clause.

The row variable can store the field values of the row, but it has no intrinsic connection with a database column. Once the row variable contains the correct field values, you must then save the variable into the ROW column with one of the following SQL statements:

To update the ROW column in the table with contents of the row variable, use an UPDATE statement on a table or view name and specify the row variable in the SET clause. (For more information, see Updating ROW-Type Columns.)
To insert a row into a column, use the INSERT statement on a table or view name and specify the row variable in the VALUES clause. (For more information, see Inserting Values into ROW-Type Columns.)

For examples of SPL ROW variables, see the HCL OneDB™ Guide to SQL: Tutorial. For more information on using row variables, see the discussion of complex data types in the HCL OneDB ESQL/C Programmer's Manual.