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 following diagram below for the syntax and an example of updating records within object tables:
The UPDATE statement specifying the table
SET attribute name equal to sub query
From table name
where clause for the sub query to retrieve the reference information
WHERE clause for the update statement
An example of using a sub query to update a record is illustrated by the following diagram:
UPDATE product_obj_table p1
SET package_id = (SELECT REF(p2)
FROM product_obj_table p2
WHERE product_id = 14)
WHERE product_id = 22;
The UPDATE statement
SET package_id =SELECT
FROM product_obj_table
WHERE product_id =14
WHERE product_id=22
Updating with Query Syntax
UPDATE <table_name> <table_alias>
SET<attribute_name> =(SELECT REF(alias)
from <table_name> <table_alias>
WHERE <cluase>)
WHERE <clause>;
Syntax to update with Query
Location 1
The UPDATE statement specifying the table
Locations 2-4
The sub query to retrieve the reference information
Location 5
The WHERE clause
In the next lesson, you will learn to write DML statements to delete records from object tables.
