delet rows from multiple tables with a single query
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
deleting rows from multiple tables with a single query
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