Home » SQL & PL/SQL » SQL & PL/SQL » deleting records in a table based on a collection
deleting records in a table based on a collection [message #252244] Wed, 18 July 2007 05:22 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am using Oracle 10g release 2.

I wanna delete records in a table which do not match the records in the collection.

Since the collection is a pl/sql collection I cannot do it like

v_t v_type;

DELETE FROM sample WHERE flex_name NOT IN (SELECT * FROM TABLE(v_type(SELECT * FROM v_t)));

is there any other alternative to do the same which is also efficient in performance ?

Re: deleting records in a table based on a collection [message #252257 is a reply to message #252244] Wed, 18 July 2007 07:04 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, you can directly use (assuming your type is correct):
DELETE FROM sample WHERE flex_name NOT IN (SELECT * FROM TABLE(v_t));

Then, assuming there is no NULL in v_t, there is the equivalent:
DELETE FROM sample 
WHERE flex_name IN 
(select flex_name from sample minus select * from table(v_t));

Regards
Michel
Previous Topic: bulk collect updates using joins
Next Topic: CONCAT & SQL (to display rows into columns)
Goto Forum:
  


Current Time: Thu Dec 08 20:11:05 CST 2016

Total time taken to generate the page: 0.09080 seconds