How to delete record for multiple table in one query using oracle

Question

For Example I have execute this sql query which is given a result. I want to delete those result using oracle query.

 Select 
a.ENAME    ,
a.JOB,
a.MGR,
a.HIREDATE,
a.SAL,
b.DEPTNO,
b.DNAME,
b.LOC
from emp a , dept b where a.DEPTNO=b.DEPTNO and a.DEPTNO=10
DELETE FROM emp , dept 
WHERE a.DEPTNO=b.DEPTNO

Answer 1

There is not any command to delete from two tables link that. But I propose two scenarios. First scenario can be used for non parent/child tables. Second scenario can be used for parent/child tables.

First: Gather all your common PK's or any common field of your tables. (Surely you have a common field). like below query:

(Select a.DEPTNO from emp a , dept b where a.DEPTNO=b.DEPTNO and a.DEPTNO=10)

Then use sequence of delete commands in one transaction.

delete from dept where DEPTNO IN (Select a.DEPTNO from emp a , dept b where a.DEPTNO=b.DEPTNO and a.DEPTNO=10)
delete from emp where DEPTNO IN (Select a.DEPTNO from emp a , dept b where a.DEPTNO=b.DEPTNO and a.DEPTNO=10)

Second: use CASCADE DELETE configuration. (just for parent/child tables)
This solution is harmful, if side effects could not be controled.
In cascade delete, when a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.