John Paul-702936 (2/11/2010)
...But .. The Script i am looking for is ..
I want to delete the records from table2 which are in table1 ..mean matching all the columns
So .. i updated script like
Delete from table2
where exists (select * from table1)
Just want to know whether it is Correct or not ..please correct me if this is wrong Please ...
Hi John Paul,
No, the example you give will not do what you expect it to do. The delete-statement you have written is "if there are any rows in table1, delete all rows in table 2". You need to add some conditions, linking the rows in table1 to the rows in table2:
delete from table2 t2
where exists (select top 1 1 from table1 t1 where t1.col1 = t2.col1 and t1.col2 = t2.col2 [...])
Note: Allways use TOP together with EXISTS to prevent the database engine to fetch all rows in table1 - which can be a lot of rows - when you only need to know that there are at least one...
/Markus