delete multiple tables with single query

[srinivasm]

Hi

Can we delete rows from multiple tables with a single query (like we are retrieving rows from multiple tables using select statement) ?

for example:

these are my tables

SQL> select * from test12;

ID NAME
--------- ----------
100 srini
200 venu
300 vega

SQL> select * from test13;

ID ADDRESS
--------- ----------
300 add1
200 add2
100 add3


and i have tried to delete as follows

SQL> delete test12,test13 where test12.id=300 and test12.id=test13.id;
delete test12,test13 where test12.id=300 and test12.id=test13.id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


this is the problem


thanks for any help

SrinivasM

[Dave_A]

There are a couple of problems with your delete statements:

1) You can only delete from one table with a single delete statement

2) The keyword FROM was missing - eg.

delete from test12 where ...

If you need to delete from both tables, then is it possible to use a foreign key constraint with on delete cascade?

Failing that, I guess you are going to have to write a PL/SQL function to do the job.

HTH

David.

[srinivasm]

Hi David,

Can you give a sample query how to use the cascade with delete statement.

I have tried like this , but it is failing

---------------------------------------------------------------------
SQL> create table test14 (id number primary key,name varchar2(10));

Table created.

SQL> create table test15 (id number,address varchar2(10), foreign key (id) references test14(id));

Table created.

-----------------------------------------------------------------------

these are the rows in the tables

--------------------------------------------------------------------

SQL> select * from test14;

ID NAME
--------- ----------
100 srini
200 vijay
300 ramu

SQL> select * from test15;

ID ADDRESS
--------- ----------
100 add1
200 add2
300 add3
------------------------------------------------------------------------

I have tried to delete the rows with cascade



SQL> delete from test14 where id=300 cascade;
delete from test14 where id=300 cascade
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


Thankyou


SrinivasM

[Dave_A]

Hi,

The on delete cascade option is part of the constraint definition, not the delete command - here is a paste from the Oracle documentation (V7 unfortunately - the only one I have handy at the mo :( ), but it should apply equally to Oracle 8...
-----------------
Maintaining Referential Integrity with the ON DELETE CASCADE Option
If you use the ON DELETE CASCADE option, Oracle7 permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.
Example VII

This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:


CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE )
Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.

-------------

HTH

David.

[srinivasm]

HI David ,

I got it. It is like below

--------------------------------------------------------------------
SQL> create table test16 (id number primary key,name varchar2(10));

Table created.


SQL> create table test17 (id number,address varchar2(10), foreign key (id) references test16(id) on
delete cascade);

Table created.


------------------------------------------------------------------------
these are the rows of the tables

SQL> select * from test16;

ID NAME
--------- ----------
100 srini
200 ram
300 sam

SQL> select * from test17;

ID ADDRESS
--------- ----------
100 add1
200 add2
300 add3

-----------------------------------------------------------------------

deleting the row in one table , so that it will delete the foreign keys of the child table.

SQL> delete from test16 where id=300;

1 row deleted.

---------------------------------------------------------------------
And checking whether the rows are deleted or not

SQL> select * from test16;

ID NAME
--------- ----------
100 srini
200 ram

SQL> select * from test17;

ID ADDRESS
--------- ----------
100 add1
200 add2



Thank you David

SrinivasM