Updating a Row Variable (IDS, ESQL/C)

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.

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 ESQL/C 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 ESQL/C 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 (IDS).)
• 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 (IDS).)

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

Related Information