Updating Rows in a Nested Table in Oracle 23ai

Updating data within a nested table requires the TABLE() operator — the same operator used for nested table INSERT and DELETE operations covered in earlier lessons. The TABLE() operator enables a technique called flattening^[1], which allows Oracle to treat the nested table as a queryable and modifiable set by wrapping a parent row subquery inside the TABLE() function. Without flattening, Oracle has no mechanism to direct DML at specific rows within the nested table storage — the nested table elements exist in a separate storage table and are not directly addressable as columns of the parent row.

To update a particular record within a nested table, the UPDATE statement must identify the parent row through a subquery and then apply a SET clause and an optional WHERE clause to the nested table rows returned by that subquery.

Nested Table UPDATE — Concrete Example

The following statement updates the detail_nest nested table column within the SALE_HEADER table. It targets the nested table for sale ID 36 and updates the item_qty value for the row where detail_amount equals 55:

UPDATE TABLE(SELECT s.detail_nest
             FROM   sale_header s
             WHERE  s.sale_id = 36)
SET    item_qty = 406
WHERE  detail_amount = 55;

This statement operates in two stages. The subquery inside TABLE() identifies the parent row — the sale record with ID 36 — and retrieves its detail_nest nested table. The outer UPDATE then applies the SET and WHERE clauses to the rows within that nested table, updating item_qty to 406 for any nested row where detail_amount is 55.

UPDATE Syntax for Nested Tables

The general syntax for updating rows within a nested table using the TABLE() operator is:

UPDATE TABLE(SELECT <nested table column>
             FROM   <parent table name> <table alias>
             WHERE  <parent row condition>)
SET    <nested table column> = <value>
WHERE  <nested row condition>;

Each component serves a distinct purpose in the statement:

Updating Multiple Columns in a Single Statement

The SET clause can update multiple nested table columns simultaneously. Using the same SALE_HEADER schema, the following statement updates both item_qty and detail_amount for a specific nested row:

UPDATE TABLE(SELECT s.detail_nest
             FROM   sale_header s
             WHERE  s.sale_id = 36)
SET    item_qty      = 500,
       detail_amount = 75.00
WHERE  item_qty = 406;

The outer WHERE clause filters on the current value of item_qty within the nested table — the value set in the previous example. Both columns are updated atomically for all matching nested rows.

Updating All Nested Rows for a Parent

Omitting the outer WHERE clause updates all rows in the nested table for the identified parent row. This is appropriate when a blanket update to all nested elements is required:

UPDATE TABLE(SELECT s.detail_nest
             FROM   sale_header s
             WHERE  s.sale_id = 36)
SET    item_qty = 0;

This sets item_qty to 0 for every row in the detail_nest nested table associated with sale ID 36. All other parent rows and their nested tables are unaffected.

Bulk Updates with FORALL

When the same nested table update must be applied across many parent rows, the FORALL statement reduces context switches between the PL/SQL and SQL engines by sending the full set of DML operations in a single round trip:

DECLARE
    TYPE id_list IS TABLE OF sale_header.sale_id%TYPE;
    v_sale_ids id_list;
BEGIN
    SELECT sale_id
    BULK COLLECT INTO v_sale_ids
    FROM   sale_header
    WHERE  sale_date >= DATE '2025-01-01';

    FORALL i IN 1..v_sale_ids.COUNT
        UPDATE TABLE(SELECT s.detail_nest
                     FROM   sale_header s
                     WHERE  s.sale_id = v_sale_ids(i))
        SET    item_qty = item_qty * 1.10
        WHERE  detail_amount > 50;

    COMMIT;
END;
/

This block collects all sale IDs from 2025 onward, then applies a 10% quantity increase to all nested detail rows with a detail_amount greater than 50, across every qualifying sale. The FORALL approach is significantly more efficient than an equivalent row-by-row PL/SQL loop for large result sets.

VARRAY as a Datatype in Oracle 23ai

Understanding where varrays fit as a datatype clarifies why varray updates work differently from nested table updates — a distinction the next lesson addresses directly. VARRAY stands for variable-size array. It is a bounded, ordered collection with a fixed maximum element count defined at type creation. In Oracle 23ai, the VARRAY datatype can be used in five contexts:

1. A column in a relational table — the varray is stored as part of the parent row, either inline as raw data (declared size 4 KB or less) or as BLOB data with the first 4 KB inline and the remainder out of line for larger declarations.
2. A PL/SQL variable — a varray variable can be declared and manipulated within any PL/SQL block, function, or procedure.
3. A PL/SQL procedure or function parameter — varrays can be passed as IN, OUT, or IN OUT parameters to named PL/SQL subprograms.
4. A PL/SQL function return type — a function can return a varray, allowing collection-valued results to be returned from PL/SQL subprograms.
5. A data attribute of an object type — a varray type can be declared as an attribute within a user-defined object type, enabling complex nested structures.

The inline storage model for varrays — where the collection is stored as a single unit within or adjacent to the parent row — means that modifying varray elements requires replacing the entire collection through the parent row UPDATE statement. This is fundamentally different from nested table UPDATE, which targets individual rows in a separate storage table through the TABLE() operator. The next lesson demonstrates varray update patterns in detail.