DML statements for object tables that have references to other objects require the use of a sub query within the UPDATE statement.
In the case of object tables with either associated object types or references to other objects, table aliases must be used within the UPDATE statements.
For example, within the PRODUCT_OBJ_TABLE table, PACKAGE_ID refers to the PRODUCT_TYPE object type.
To update this column, you must first perform a query to retrieve the reference value. An example of such an UPDATE statement looks like this:
UPDATE product_obj_table p1
SET package_id = (SELECT REF(p2) FROM product_obj_table
p2 WHERE product_id = 4)
WHERE product_id = 36;
Take a look at the MouseOver below for the syntax and an example of updating records within object tables:
SQL Update Query Syntax
An example of using a sub query to update a record is illustrated by the following MouseOver:
SQL Update QuerySyntax
In the next lesson, you will learn to write DML statements to delete records from object tables.