Home » SQL & PL/SQL » SQL & PL/SQL » Delete from 2 tables at the same time (Oracle 10g)
Delete from 2 tables at the same time [message #312749] Wed, 09 April 2008 13:01 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
I need to delete 2 tables at the same time. I do not wish to use temp tables. Is there any other way I can do it.

Basically the condition to delete is based on a select.

dELETE
FROM detail_tbl
WHERE accntid IN (
SELECT accntid
FROM ACCOUNT A, B,C
where ...)

dELETE
FROM summary_tbl
WHERE accntid IN (
SELECT accntid
FROM ACCOUNT A, B,C
where ...)

So I want to do the expensive select(below) just once. Be able to store this value somehow and delete from that. THe temp table, would have been perfect but I do not want commit in my query. Also cant seem to use "WITH CLAUSE"

SELECT accntid
FROM ACCOUNT A, B,C
where ...

Any hints?

thanks
Re: Delete from 2 tables at the same time [message #312752 is a reply to message #312749] Wed, 09 April 2008 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL Experts
Advanced Oracle PL/SQL questions - stored procedures, functions, packages and triggers. Newbies should not post to this forum!

Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Delete from 2 tables with 1 select [message #312754 is a reply to message #312749] Wed, 09 April 2008 13:06 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
I need to delete 2 tables based on only 1 select statement. I do not wish to use temp tables. Is there any other way I can do it.

Basically the condition to delete is based on a select.

dELETE
FROM detail_tbl
WHERE accntid IN (
SELECT accntid
FROM ACCOUNT A, B,C
where ...)

dELETE
FROM summary_tbl
WHERE accntid IN (
SELECT accntid
FROM ACCOUNT A, B,C
where ...)

So I want to do the expensive select(below) just once. Be able to store this value somehow and delete summary and detail from that. THe temp table, would have been perfect but I do not want commit in my query.
SELECT accntid
FROM ACCOUNT A, B,C
where ...


Also cant seem to use "WITH CLAUSE"

with subquery as
(SELECT accntid
FROM ACCOUNT A, B,C
where ...)
delete from summary where a.acctid in SELECT accntid from subquery),
delete from detail where a.acctid in SELECT accntid from subquery)

Something like that?

Any hints? Or a work aound would be appreicated

thanks
Re: Delete from 2 tables at the same time [message #312756 is a reply to message #312749] Wed, 09 April 2008 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above.
Re: Delete from 2 tables with 1 select [message #312757 is a reply to message #312754] Wed, 09 April 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't delete in 2 tables at the same time.
If getting the id is expensive record them in a temporary or a PL/SQL table (depending on the number of id you expect).

Regards
Michel

[Updated on: Wed, 09 April 2008 13:29]

Report message to a moderator

Re: Delete from 2 tables at the same time [message #312759 is a reply to message #312749] Wed, 09 April 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
In My Opinion, any such "requirement" screams an application design flaw & tables not normalized to 3rd Normal Form.
Re: Delete from 2 tables with 1 select [message #312804 is a reply to message #312757] Wed, 09 April 2008 16:30 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel Cadot wrote on Wed, 09 April 2008 11:10
You can't delete in 2 tables at the same time.

Can't or shouldn't?

Considering the posters concern for performance, the following example is likely a poor choice, but just for fun, here is a way to delete rows from two tables with a single delete statement (although in reality much more work is being done behind the scenes in the trigger and thus likely to produce a less efficient overall solution).

This approach can also be trickier to maintain later, given the *hidden* deletes in the trigger.

CMERRY@ORA102> CREATE TABLE t1 AS SELECT ROWNUM c FROM DUAL CONNECT BY ROWNUM <= 5;

Table created.

CMERRY@ORA102> c/t1/t2
  1* CREATE TABLE t2 AS SELECT ROWNUM c FROM DUAL CONNECT BY ROWNUM <= 5
CMERRY@ORA102> /

Table created.

CMERRY@ORA102> CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;

View created.

CMERRY@ORA102> CREATE OR REPLACE TRIGGER v1_trig INSTEAD OF DELETE ON v1
  2  BEGIN
  3    DELETE FROM t1 WHERE c = :OLD.c;
  4    DELETE FROM t2 WHERE c = :OLD.c;
  5  END;
  6  /

Trigger created.

CMERRY@ORA102> SELECT 'T1', c FROM t1 
  2  UNION ALL
  3  SELECT 'T2', c FROM t2;

'T          C
-- ----------
T1          1
T1          2
T1          3
T1          4
T1          5
T2          1
T2          2
T2          3
T2          4
T2          5

10 rows selected.


CMERRY@ORA102> delete from v1 where c = 3;

1 row deleted.

CMERRY@ORA102> SELECT 'T1', c FROM t1 
  2  UNION ALL
  3  SELECT 'T2', c FROM t2;

'T          C
-- ----------
T1          1
T1          2
T1          4
T1          5
T2          1
T2          2
T2          4
T2          5

8 rows selected.


*Generally* the best approach is to use the associative array suggested by Michel, perhaps with a combination of BULK COLLECT and FORALL statements.
Re: Delete from 2 tables with 1 select [message #312819 is a reply to message #312804] Wed, 09 April 2008 19:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You still delete rows from one table at a time. The fact that this is done by executing a single command does not change this.
This is no different then executing a stored procedure containing two separate delete statements.

[Updated on: Wed, 09 April 2008 19:26]

Report message to a moderator

Re: Delete from 2 tables with 1 select [message #312851 is a reply to message #312819] Wed, 09 April 2008 22:47 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Frank wrote on Wed, 09 April 2008 17:25
You still delete rows from one table at a time. The fact that this is done by executing a single command does not change this.

Absolutely, in fact that is precisely what I referred to.

Frank wrote on Wed, 09 April 2008 17:25
This is no different then executing a stored procedure containing two separate delete statements.

I disagree with that. Duplicating the SELECT in both statements will cause twice the work when gathering the rows to delete. The use of the trigger allows a single gathering of rows to delete; unfortunately, the DELETE statements will be executed over and over for each row, which is the reason associative arrays are generally the best approach. The use of INSTEAD OF triggers is really just a matter of convenience and modularity much like other uses of views.
Re: Delete from 2 tables with 1 select [message #312880 is a reply to message #312851] Thu, 10 April 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anything may be done with instead of trigger and of course most are wrong way to do this including what you posted.
You break the relational model.
You break performances.
You hide what is really done.
You break maintenance.
You introduce magic.
You forbid any fix on table1 data.
You don't address the case of table2.
...

I think I can spend the whole to find reasons to NOT do that.

Regards
Michel
Re: Delete from 2 tables at the same time [message #312888 is a reply to message #312749] Thu, 10 April 2008 01:08 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel, thank you for the repeating all the negatives I had already specified.
Re: Delete from 2 tables at the same time [message #312895 is a reply to message #312888] Thu, 10 April 2008 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You said it? I admit I didn't read it I just saw a wrong code and example.
NEVER post wrong code in Newbie forum, all what is seen is the code that seems to work not the warnings. The proof I didn't even read what you said, just the code, so what about a newbie that just want code and quick answer to his question and not want to spend time to understand or search an alternative solution?

Regards
Michel

Re: Delete from 2 tables at the same time [message #312899 is a reply to message #312895] Thu, 10 April 2008 01:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm.. So the fact that you did not read the complete post is due to the post?
Re: Delete from 2 tables at the same time [message #312935 is a reply to message #312895] Thu, 10 April 2008 03:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Thu, 10 April 2008 16:23

NEVER post wrong code in Newbie forum, all what is seen is the code that seems to work not the warnings.

I disagree. I UNDERSTAND, but I disagree.

A tenacious newbie will keep searching for a solution. cmerry's solution is not novel - there are plenty of similar examples out there to be found - and the newbie just might find them.

I think it better to identify the POSSIBLE solutions with warnings as to why they may or may not be appropriate. This way, when Google spits up this thread in the future along with the others that propose the same solution WITHOUT warnings, the searcher at least has the benefit of our opinion.

Personally, I would have used stronger wording than @cmerry (eg. "You would be an idiot to try this, and will be forever vilified by your peers"). But that's just me.

Ross Leishman
Re: Delete from 2 tables at the same time [message #312963 is a reply to message #312935] Thu, 10 April 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By experience, I can say that when you say: "In order of preference do that or thator that, and if nothing work do that one but there are drawbacks" if the latter one is easier and/or faster and/or don't required work from the requester then it is chosed whatever are the drawbacks (which are ignored).
I saw it soo many times that now I omit the last option every time.

Regards
Michel
Re: Delete from 2 tables at the same time [message #312974 is a reply to message #312749] Thu, 10 April 2008 04:32 Go to previous messageGo to next message
mohdbfaq
Messages: 5
Registered: April 2008
Junior Member
Use BULK collect and BULK Delete in PL/SQL

declare
TYPE numtab is TABLE OF number;
nt1 numtab;
cursor c1 is SELECT accntid FROM ACCOUNT where A=1;
BEGIN
OPEN C1;
LOOP
fetch c1 into nt1 LIMIT 10000;
exit when c1%notfound;
for all i1 IN nt1.FIRST..nt1.LAST LOOP
DELETE detail_tbl WHERE accntid=nt1(i1);
DELETE summary_tbl WHERE accntid=nt1(i1);
END LOOP;
close c1;
END;
/



Re: Delete from 2 tables at the same time [message #312994 is a reply to message #312974] Thu, 10 April 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use cursor loop when you can use SQL.

Regards
Michel
Re: Delete from 2 tables at the same time [message #313008 is a reply to message #312974] Thu, 10 April 2008 06:10 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Syntactically incorrect, unformatted, and doesn't improve on any answer already given.
Previous Topic: Got Problem in Reading Data From Text Files Using UTL_FILE Package in Oracle 10g Environment
Next Topic: ORACLE QUERY
Goto Forum:
  


Current Time: Thu Dec 08 00:35:17 CST 2016

Total time taken to generate the page: 0.08838 seconds