Delete statement on multiple table

Posted: December 28, 2012 in Programming
Tags: , , , ,

Consider the following sample tables

table1 :

emp_id emp_name emp_sal

table 2:

emp_id emp_details

Now, here is a situation in which i need to delete values of table 1 which are in table 2..
For Example:

table 1:

emp_id emp_name emp_sal
   1    aaa      1000
   2    bbb      2000
   3    ccc      3000

table 2:
emp_id emp_details
   2      xxx
   3      yyy

Here i need to delete emp ids 2 and 3 from table 1.
This is a most common scenario, where we need to delete or update values from 1 table using another.
In this post we gonna see, how could we achieve that.
Till today, i was using a very easy sql sequence which performs “IN” function whenever i held up with these scenarios.

delete from table1 where emp_id in (select emp_id from table2);

But, think of a situation where there are more than million rows, i feel these IN statements to be ineffective . Whats the solution?
we can go for “where exists”, which run relatively faster. Here is the sample query

delete from table1 where exists ( select table2.emp_id from table2 where table2.emp_id = table1.emp_id);

simillarly, the update can be done as

update table1 set table1.emp_sal = 2000 where exists ( select table2.emp_id from table2 where table2.emp_id = table1.emp_id );

this will set all the employees salary who are in table 2 as 2000. add the necessary condition inside the sub query.

Advertisements

Comments are closed.