Reuse of select query result oracle

Question

I've got following query

SELECT ID FROM MARMELADES mrm 
where not exists 
    (SELECT 1 FROM TOYS toys 
    WHERE mrm.ID = toys.ID 
    AND mrm.INGREDIENT = toys.INGREDIENT 
    AND mrm.BOX_TYPE = 2)
AND mrm.BOX_TYPE = 2

It returns almost 400+ results of id, for example [12, 33, 45, ... , 3405]

Now, i want to remove all ids that are from that list everywhere from my database. this is not only MARMELADES and TOYS. Also, i have for example 35+ tables where i can have this id).

I would be happy if this query could extract in some functions like ALL_UNNEEDED_IDS so i can use it like this:

DELETE FROM ANOTHER_TABLE_1 WHERE ID IN ( ALL_UNNEEDED_IDS ) 
DELETE FROM ANOTHER_TABLE_2 WHERE ID IN ( ALL_UNNEEDED_IDS ) 
DELETE FROM ANOTHER_TABLE_3 WHERE ID IN ( ALL_UNNEEDED_IDS ) 
DELETE FROM ANOTHER_TABLE_4 WHERE ID IN ( ALL_UNNEEDED_IDS ) 
...
DELETE FROM ANOTHER_TABLE_35 WHERE ID IN ( ALL_UNNEEDED_IDS )

It is possible to do it in oracle to reuse such results?

If all these tables are relaeted by this ID as an FK, sounds like you should configure them for cascading delete. Otherwise, insert your query results into a temp table, then reference that from all the delete calls. — OldProgrammer, Aug 08 '16 at 14:49
Check this out: http://stackoverflow.com/questions/9119465/how-to-reuse-a-large-query-without-repeating-it — Mathews Mathai, Aug 08 '16 at 14:53
For Oracle:http://stackoverflow.com/questions/2501683/reuse-select-query-in-a-procedure-in-oracle — Mathews Mathai, Aug 08 '16 at 14:53
Using the Oracle solution @MathewsMathai mentioned, you don't even need to create a new type. You can use an existing Oracle type such as `KU$_OBJNUMSET`. — Tony Andrews, Aug 08 '16 at 14:57

score 0 · Accepted Answer · answered Aug 08 '16 at 16:42

Use the first query within your subsequent queries. IE:

DELETE FROM ANOTHER_TABLE_1 WHERE ID IN (
     SELECT ID FROM MARMELADES mrm 
     where not exists 
         (SELECT 1 FROM TOYS toys 
         WHERE mrm.ID = toys.ID 
         AND mrm.INGREDIENT = toys.INGREDIENT 
         AND mrm.BOX_TYPE = 2)
     AND mrm.BOX_TYPE = 2
);

When you get to the toys and marmelades tables, you'll need a temporary holder table as @Gordon suggests.

Reuse of select query result oracle

1 Answers1