In this lesson, we will look at updating data within a nested table. To update the data within a nested table, the DML statement should have the THE or the TABLE keywords. To update a particular record within the nested table, you must select a particular record within the main table and the nested table. This calls for flattening the DML statement. Flattening requires the use of the THE and the TABLE keywords.
To query, update, insert, or to delete records from the nested table, it must be queried as if it were a single table. This is achieved by the usage of "THE" or "TABLE" functions. Let us look at the following DML statement, which updates the nested table within the SALE_HEADER table:
UPDATE TABLE (SELECT s.detail_nest FROM SALE_HEADER S
WHERE s.sale_id = 35)
SET item_qty = 56WHERE detail_amount = 55;
In the example above, we first selected the nested table from the SALE_HEADER table, and then updated the nested table.
The following Tooltip(Mouseover) explains the syntax for updating rows within a nested table:
The UPDATE statement with the TABLE keyword
The SELECT statement for selecting a particular record from the parent table
The SET clause to update the value within a nested table
The WHERE clause to select a particular row of the nested table
UPDATE TABLE (SELECT <table_name>
FROM <parent table name> <table alias>
WHERE <clause>)
SET (nested table) a <variable>
WHERE <clause>;
Updating RowsWithin Nested TableSyntax
The following diagram shows an example of updating rows within a nested table:
UPDATE TABLE (SELECT s.detail_nest FROM
SALE_HEADER S
WHERE s.sale_id = 36)
SET item_qty = 406
WHERE detail_amount = 55;