Join in DELETE CLAUSE :
There are situation where we need to delete
records based on one or other condition and that in turn depends on joining of
tables.
We start wondering whether DELETE statement hold
joins??
Yes you can use join with delete statement, let’s
have a small practical.
I am having two table ‘PARENT_TAB’ and ‘CHILD_TAB’
where ‘first_id’ is the Primary key in PARENT_TAB and same is the Foreign Key
in CHILD_TAB.
The data in the table are as follows
select * from parent_tab
first_id name
-----------
--------------------------------
2 nimesh
3 pratik
(2 row(s) affected)
select * from child_tab
second_id first_id
dept
----------- ----------- ----------------
3 2 DEVLOPMENT
5 2 DESIGNING
(2 row(s) affected)
Q Now the
question is how to delete record using join in delete statement from CHILD_TAB
where the candidate name is ‘nimesh’.
delete parent_tab
from parent_tab
p, child_tab c
where p.first_id=c.first_id and p.name='nimesh'
It is simple
as writing select statement, add FROM clause give the table name you wanna join
with alias and then in WHERE clause join the table and put the condition.
After
executing above DELETE statement, execute the following statement
select * from child_tab
second_id first_id
dept
----------- -----------
--------------------------------------------------
(0 row(s) affected)
Since both the
record belongs to candidate ‘nimesh’ both get deleted.
Post Reference: Vikram Aristocratic Elfin Share
