PL/SQL   «Prev  Next»

Lesson 7Deleting rows in a nested table or a varray
Objective Write a delete Command for a nested table or Varray

Write delete Command for nested table or Varray

Deleting data from a nested table requires the use of a DELETE statement, whereas deleting data from a varray requires the value of the varray to be set to NULL via an UPDATE statement.
To drop a particular nested table, you can use the DELETE statement with the THE or the TABLE keyword, as the following example demonstrates: 

DELETE TABLE (SELECT d.detail_nest FROM SALE_HEADER d 
WHERE d.SALE_ID = 35) p 
WHERE p.item_qty = 1;

This DML statement selects a particular record from the nested table for a particular record within the main table and deletes the record. To delete the elements of a varray, the varray must be set to NULL, as the following example shows: 
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array = NULL
WHERE cust_id = 25;

The DML statement above deletes all the elements of a varray.
The following MouseOver shows the syntax of deleting data from a nested table:
Delete table syntax
  1. The DELETE statement with the TABLE or the THE keyword
  2. The SELECT statement to select a particular record from the parent table
  3. The WHERE clause to select a particular row of the nested table

DELETE TABLE(SELECT <nested table name>
  FROM <parent table name> <table alias>
  WHERE <clause>) <nested table alias>
WHERE <clause>;
The DELETE statement with the TABLE or the THE keyword

Updating Within Varray Syntax
In the next lesson, you will learn how to delete entire nested tables and varrays.