WHATS WRONG WITH THE BELOW QUERY

Nope Its working as per design

It interprets memberid column as that sent from outer table ie testx. Hence so far as you've a row in testy it will just do a trivial check ie Memberid = Memberid from testx table which is always true and hence it deletes all the records. The above IN based condition works equivalent to this

DELETE FROM [dbo].testx WHERE EXISTS ( SELECT 1 FROM  testy (NOLOCK)) 

ie so long as there's a record in testy it will go ahead deleting all rows from testx

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c