RelationalDBDesignRelationalDBDesign


PL/SQL   «Prev  Next»
Lesson 6Delete an entire Array or nested Table
ObjectiveWrite a delete command to remove all rows from a nested table or an Array

Delete Command to remove all Rows from a nested Table or an Array

To delete all the data from a nested table, you can use the DELETE statement with the THE or the TABLE keyword, as following example shows:
DELETE TABLE(SELECT d.detail_nest FROM SALE_HEADER d 
WHERE d.sale_id = 35);
The DML statement above deletes all the elements from a nested table.
You can also delete all the elements by updating the nested table to NULL, as the following example shows:

UPDATE SALE_HEADER
SET detail_nest = NULL
WHERE sale_id = 35);


The DML statement above updates the nested table to NULL, which in turn deletes all the elements within the table.
Now that you have learned few techniques for deleting rows from nested tables and varrays, try them yourself with the following tutor-based exercise. In this exercise, you will be asked to build a DELETE statement to delete the records from the SALE_HEADER table.

Delete Nested Table - Exercise

Click the Exercise link below to build the DELETE statement.
Delete Nested Table - Exercise