Delete from 2 tables at the same time [message #312749] |
Wed, 09 April 2008 13:01  |
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
|
|
|
|
Delete from 2 tables with 1 select [message #312754 is a reply to message #312749] |
Wed, 09 April 2008 13:06   |
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 with 1 select [message #312804 is a reply to message #312757] |
Wed, 09 April 2008 16:30   |
 |
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 at the same time [message #312935 is a reply to message #312895] |
Thu, 10 April 2008 03:00   |
rleishman
Messages: 3728 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 #312974 is a reply to message #312749] |
Thu, 10 April 2008 04:32   |
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;
/
|
|
|
|
|