Multiple Expressions with IN

Question

I have two somewhat large (4+ million records) tables with the same structure, and they have about 300 thousand duplicate rows. I would like to DELETE duplicate rows using the DELETE IN syntax.

I already did this with the MERGE instruction (available only in 2008 or later, so I can’t use it since I am still running 2005) and DELETE EXISTS , but I am running into some problems with DELETE IN .

The problem with DELETE IN is that my large table has a composite primary key, that is, I can only identify unique rows using all of these columns together.

Is it possible in T-SQL to have multiple expressions as parameters for an IN clause? Something like:

 DELETE FROM MyBigTable WHERE ([Column1], [Column2], [Column3]) IN (SELECT [Column1], [Column2], [Column3] FROM MyBigTable INTERSECT SELECT [Column1], [Column2], [Column3] FROM MyOtherBigTable) 

Answer 1

You can just do a JOIN for this:

 DELETE A FROM MyBigTable A INNER JOIN MyOtherBigTable B ON A.Column1 = B.Column1 AND A.Column2 = B.Column2 AND A.Column3 = B.Column3