Update rows of a supertable

When you update the rows of a supertable, the scope of the update is a supertable and its subtables.

When you construct an UPDATE statement on a supertable, you can update all columns in the supertable and columns of subtables that are inherited from the supertable. For example, the following statement updates rows from the employee and sales_rep tables, which are subtables of the supertable person:

UPDATE person
   SET salary=65000
   WHERE address.state = 'CA';

However, an update on a supertable does not allow you to update columns from subtables that are not in the supertable. For example, in the previous update statement, you cannot update the region_num column of the sales_rep table because the region_num column does not occur in the employee table.

When you perform updates on supertables, be aware of the scope of the update. For example, an UPDATE statement on the person table that does not include a WHERE clause to restrict which rows to update, modifies all rows of the person, employee, and sales_rep table.

To limit an update to rows of the supertable only, you must use the ONLY keyword in the UPDATE statement. For example, the following statement updates rows of the person table only:

UPDATE ONLY(person)
   SET address = ROW('14 Jackson St', 'Berkeley',
   address.state, address.zip)
   WHERE name = 'Sallie, A.';

Important: Use caution when you update rows of a supertable because the scope of an update on a supertable includes the supertable and all its subtables.