Updating ROW-Type Columns (IDS)

Use the SET clause to update a named or unnamed ROW-type column. For example, suppose you define the following named ROW type and a table that contains columns of both named and unnamed ROW types:

CREATE ROW TYPE address_t 
(
   street CHAR(20), city CHAR(15), state CHAR(2)
);
CREATE TABLE empinfo 
(
   emp_id INT
   name ROW ( fname CHAR(20), lname CHAR(20)),
   address address_t
);

To update an unnamed ROW type, specify the ROW constructor before the parenthesized list of field values.

The following statement updates the name column (an unnamed ROW type) of the empinfo table:

UPDATE empinfo SET name = ROW('John','Williams') WHERE emp_id =455

To update a named ROW type, specify the ROW constructor before the list (in parentheses) of field values, and use the cast ( :: ) operator to cast the ROW value as a named ROW type. The following statement updates the address column (a named ROW type) of the empinfo table:

UPDATE empinfo
SET address = ROW('103 Baker St','Tracy','CA')::address_t
WHERE emp_id = 3568

For more information on the syntax for ROW constructors, see Constructor Expressions (IDS). See also Literal Row.

The ROW-column SET clause can only support literal values for fields. To use an ESQL/C variable to specify a field value, you must select the ROW data into a row variable, use host variables for the individual field values, then update the ROW column with the row variable. For more information, see Updating a Row Variable (IDS, ESQL/C).

You can use ESQL/C host variables to insert non-literal values as:

• An entire row type into a column

  Use a row variable as a variable name in the SET clause to update all fields in a ROW column at one time.

• Individual fields of a ROW type

  To insert non-literal values into a ROW-type column, you can first update the elements in a row variable and then specify the collection variable in the SET clause of an UPDATE statement.

When you use a row variable in the SET clause, the row variable must contain values for each field value. For information on how to insert values into a row variable, see Updating a Row Variable (IDS, ESQL/C).

You can use the UPDATE statement to modify only some of the fields in a row:

• Specify the field names with field projection for all fields whose values remain unchanged.

  For example, the following UPDATE statement changes only the street and city fields of the address column of the empinfo table:

  UPDATE empinfo
  SET address = ROW('23 Elm St', 'Sacramento',
                 address.state)
     WHERE emp_id = 433

  The address.state field remains unchanged.

• Select the row into an ESQL/C row variable and update the desired fields.

  For more information, see Updating a Row Variable (IDS, ESQL/C).