Home » SQL & PL/SQL » SQL & PL/SQL » Deleteing rows using with clause
Deleteing rows using with clause [message #238384] Thu, 17 May 2007 14:48 Go to next message
Messages: 90
Registered: July 2005
Can I use delete in subqury factoring.

We have a very huge master table the key columns say are key1, key2.

there are 2 detail tables. Depending on certain conditions specified in the master table say if 30000 records are retrieved, I want to tie back those records with the 2 detail tables and delete them.

Is there any way I can query the huge master table just once? Instead of doing it twice to delete from 2 separate tables.

create table master (id number(10), name varchar2(30), coast varchar(10)) ;
create table detail(id number(10), dept varchar2(20));
create table detail2(id number(10), agency varchar2(20));

insert into master values (1, 'boston', 'east');
insert into master values (2, 'NYC', 'east');
insert into master values (3, 'SFC', 'west');
insert into detail values(1,'Research');
insert into detail2 values(1,'AGENT1');

So I query the master table for the east cost. I want to delete all records from the detail tables whichis detail and detail2 for that id which is returned.

Is there any way I can use a 'WITH CLAUSE' and then delete ? Also, I would like to query this master table only once.

THanks for your time and help...
Re: Deleteing rows using with clause [message #238397 is a reply to message #238384] Thu, 17 May 2007 16:05 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I think the problem is that you need to issue two separate delete statements. I don't know how to do that in one operation. I think you can use a with clause in a subquery of a delete statement, but that really wouldn't help you.

So you need a common set of data to reference with both statements. You could I suppose create a temporary table that is skinny, just holding the id field of your query result. Then issue your two deletes based on this temp table.

But that would involve a tradeoff that may or may not matter to your data. You'd be querying the big table once instead of twice, but in exchange you'd be writing the temp table of ids once, querying from it twice, and deleting from it once.

Another option is if you had a btree index involving both the search value and the id on the big table, then you would never really need to query the big table in the first place; you would just be scanning the index twice.
Previous Topic: How to remove hyphens from a string
Next Topic: Improvising the where condition for faster query processing???
Goto Forum:

Current Time: Wed Aug 16 12:39:23 CDT 2017

Total time taken to generate the page: 0.09485 seconds