Write SQL to Update an Object Table Using a Subquery

When an object table contains a REF column — a typed pointer to a row in another object table — the UPDATE statement cannot assign a new reference value as a scalar literal. The reference must be retrieved at update time using a subquery that calls the REF() function against the referenced object table. This lesson covers the syntax, two worked examples, the runtime constraints that govern subquery-based updates, and the broader context of updating REF columns within Oracle's object-relational model.

Why a Subquery Is Required for REF Columns

A REF column stores an Oracle object identifier — an internal pointer that locates a specific row in a specific object table. This identifier is generated by Oracle and is not a value you can write as a literal. The only way to obtain a valid reference value in SQL is through the REF() function, which accepts a table alias and returns the object reference for the row currently in scope under that alias. Because REF() requires a table context, the assignment in the SET clause must be a subquery rather than a direct value.

Within PRODUCT_OBJ_TABLE, the PACKAGE_ID column is a REF that references the PRODUCT_TYPE object type. To update this column, a subquery must retrieve the reference for the target product from the same table before assigning it:

UPDATE product_obj_table p1
SET    p1.package_id = (SELECT REF(p2)
                        FROM   product_obj_table p2
                        WHERE  p2.product_id = 14)
WHERE  p1.product_id = 22;

This statement updates the package_id reference on row 22 to point to row 14 in the same table. Both the outer and inner queries target product_obj_table but use distinct aliases — p1 for the row being updated and p2 for the row being referenced — to prevent Oracle from conflating the two table instances.

Syntax Template — UPDATE with REF Subquery

Two Runtime Constraints

Two constraints govern the correctness of this pattern at runtime:

1. The subquery must return exactly one row. If the inner WHERE clause matches multiple rows, Oracle raises ORA-01427: single-row subquery returns more than one row. Always filter the subquery on a primary key or unique column to guarantee a single reference is returned. In the example above, p2.product_id = 14 filters on the primary key — one row, one reference.
2. The outer and inner aliases must be distinct. Both queries target the same table in a self-referential update. Using the same alias for both would cause Oracle to resolve the inner alias against the outer table context, producing incorrect results or an error. p1 and p2 are distinct and unambiguous throughout the statement.

Worked Example

UPDATE product_obj_table p1
SET    p1.package_id = (SELECT REF(p2)
                        FROM   product_obj_table p2
                        WHERE  p2.product_id = 14)
WHERE  p1.product_id = 22;

Second Syntax Template — Updating a REF from a Different Table

The same pattern applies when the subquery targets a different object table from the one being updated. This is the more common case — a child table holds a REF to a parent table, and the reference needs to be reassigned:

Updating a REF to a Different Object Table

In the pet store schema, PET_CARE_LOG_OBJ_TABLE contains a product_ref column that references PRODUCT_OBJ_TABLE. To reassign a log entry to a different product, the subquery retrieves the reference from PRODUCT_OBJ_TABLE and assigns it to the REF column in PET_CARE_LOG_OBJ_TABLE:

UPDATE pet_care_log_obj_table pcl
SET    pcl.product_ref = (SELECT REF(p)
                          FROM   product_obj_table p
                          WHERE  p.product_id = 34)
WHERE  pcl.log_id = 7;

The outer and inner tables are different here — pcl aliases the log table being updated, and p aliases the product table being referenced. The single-row constraint still applies: p.product_id = 34 must match exactly one row in PRODUCT_OBJ_TABLE.

Dangling References After UPDATE

When a REF column is updated to point to a new object, the old reference is simply replaced — Oracle does not cascade any changes to the previously referenced object. If the previously referenced object is later dropped using DROP USER CASCADE or a direct DELETE, any REF columns that still point to it become dangling references. A dangling reference is one that points to an object that no longer exists.

Dangling references can be detected with the IS DANGLING predicate and should be handled before any application code attempts to dereference them:

-- Identify dangling references in pet_care_log_obj_table
SELECT pcl.log_id
FROM   pet_care_log_obj_table pcl
WHERE  pcl.product_ref IS DANGLING;

In Oracle 23ai, referential integrity for REF columns can be enforced using the REFERENCES constraint on the REF column definition, which prevents the referenced object from being deleted while references to it exist — analogous to a foreign key constraint on a relational table.

Comparing Subquery UPDATE to Direct UPDATE

The distinction between a direct attribute update and a subquery-based REF update is worth summarizing explicitly:

-- Direct attribute update — no subquery needed
UPDATE customer_obj_table cot
SET    cot.full_address.zip = '09982'
WHERE  cot.cust_id = 1;

-- REF column update — subquery required
UPDATE pet_care_log_obj_table pcl
SET    pcl.product_ref = (SELECT REF(p)
                          FROM   product_obj_table p
                          WHERE  p.product_id = 34)
WHERE  pcl.log_id = 7;

The first form uses dot notation to navigate into a nested object type attribute — no subquery is involved. The second form assigns a REF value that must be obtained through REF() — a subquery is mandatory. The choice between these two forms is determined entirely by the column type being updated, not by developer preference.

Summary

Updating a REF column in an Oracle object table requires a subquery that calls REF(alias) against the referenced object table. The subquery must return exactly one row — filter on a primary key or unique column to guarantee this. The outer and inner table aliases must be distinct, even when both queries target the same table. Alias-prefix all column references in both the SET and WHERE clauses for clarity and to follow Oracle 23ai object table best practice. After updating a REF column, check for dangling references using the IS DANGLING predicate if the previously referenced object may have been removed. The next lesson covers writing DML statements to delete records from object tables.