-
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
-
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.
-
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
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|