PL/SQL   «Prev  Next»

Lesson 5Updating rows in a Nested Table
ObjectiveWrite an update command for a nested table

Updating Rows in Nested Table using 'THE' or 'TABLE' keywords

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 example
UPDATE statement with the TABLE keyword 
UPDATE TABLE (SELECT s.detail_nest 
FROM SALE_HEADER S
WHERE s.sale_id = 36)
SET item_qty = 406
WHERE 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 diagram explains the syntax for updating rows within a nested table:

Updating rows within Nested Table Syntax

update-table-syntax
  1. The UPDATE statement with the TABLE keyword
  2. The SELECT statement for selecting a particular record from the parent table
  3. The SET clause to update the value within a nested table
  4. 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) = <variable>
WHERE <clause>;


What is a VARRAY?

VARRAY stands for variable-size array. It is an array that can be either manipulated as a whole or individually as elements. It has a maximum size and can contain 0 to any number of elements up to the maximum specified. VARRAY is stored in-line and that means the data of a column of VARRAY type is stored along with the remaining data of the row.
You can use VARRAY data type for:
  1. A column in a relational table
  2. A PL/SQL variable
  3. A PL/SQL parameter of a procedure or function
  4. A PL/SQL function return type
  5. A data attribute of an object type

Example of Updating Rows within Nested Table
In the next lesson, you will learn more about updating varrays.
[1]Flattening: A relational table which is associated with a nested table consists of two tables, the main relational table and the nested table.