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:
The DELETE statement with the TABLE or the THE keyword
The SELECT statement to select a particular record from the parent table
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.