Update a row variable

The UPDATE statement and the collection-derived table clause allow you to update a particular field or group of fields in the row variable.

You specify the new field values in the SET clause. An UPDATE of a field or fields in a row variable cannot include a WHERE 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;
   int new_y;
EXEC SQL END DECLARE SECTION;
;

new_y = 4;
EXEC SQL update table(:myrect) 
   set x=3, y=:new_y;

You cannot use a row variable in the collection-derived table clause of an INSERT statement. However, you can use the UPDATE statement and the collection-derived table clause to insert new field values into a row host variable, as long as 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);