Deleting Rows That Contain Collection Data Types

When a row contains a column that is a collection data type (LIST, MULTISET, or SET), you can search for a particular element in the collection, and delete the row or rows in which the element is found.

DELETE FROM new_tab WHERE 'jimmy smith' IN set_col;

You can also use a collection variable to delete values in a collection column by deleting one or more individual elements in a collection. For more information, see Collection-Derived Table and the examples in Database Name and Example of Deleting from a Collection.