DELETE BY JOINING TABLES

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