how to delete using select? RSS

• • Reply
  

  muhammadazee...

  Member

  4 Points

  167 Posts

  how to delete using select?

  Feb 05, 2013 07:06 PM|LINK

  Hi i have 2 tables. i want to delete rows from table a that r preent in table b. EID and ID identifies the unique row in both tables. i performed it in 2 differnet ways.

  1) delete FROM tblA

       where EID in (Select EID from tblB) AND

       ID IN (Select id from tblB);

  2) DELETE A

  FROM tblA A

  inner join tblB B

  on B.EID= A.EID and B.id = A.id

  Result: query 1 gives wrong o/p while query 2 gives right. How to achive this using query 1

  Table-A				Table-B
  EID	ID	Status		EID	ID	Status
  100-100	1	n		100-100	1	Y
  100-100	2	n		100-300	2	Y
  100-200	1	n		100-200	1	Y
  100-200	2	n
  100-300	1	n
  100-300	2	n

   

   

   

   

   

   

   

  • Edited by muhammadazeemazam on Feb 05, 2013 07:06 PM
• • Reply
  

  TabAlleman

  All-Star

  15741 Points

  2724 Posts

  Re: how to delete using select?

  Feb 05, 2013 07:14 PM|LINK

  You cannot achieve it using query 1.   What is your real goal?   why not just use query 2?

  -Tab Alleman
• • Reply
  

  muhammadazee...

  Member

  4 Points

  167 Posts

  Re: how to delete using select?

  Feb 05, 2013 08:08 PM|LINK

  I was just tring to achive it through query 1. but whats the reason behind that i can't achive through query 1.

   thanks

   

• • Reply
  

  kidshaw

  Participant

  1178 Points

  252 Posts

  Re: how to delete using select?

  Feb 05, 2013 08:16 PM|LINK

  In query 2 you are linking both fields to match records between your two tables. This is the way to do it. Stop there. In query 1, you are matching each field independently. So the first ID link will always find a result because it just needs a record in table B with a matching ID. You then do the same with EID so as long as there is a record in table b with either the ID or the EID then it will be deleted. By separating the links you negate the AND and create an OR. Hope that makes sense and ties in with what you are seeing.
  If it helps, mark as answer - your approval helps with my recovery ;)
  • Marked as answer by Chen Yu - MSFT on Feb 12, 2013 12:33 PM