CODEWITHSUNDEEP
sqlt-sqlsql-server-2005sql-delete
ivanmp
ivanmp

Reputation: 529

Multiple expressions with IN

I have two somewhat big (4+ million records) tables with identical structure, and they have about 300k duplicated rows. I'd like to DELETE the duplicate rows using the DELETE IN syntax.

I've already done it using the MERGE statement (available only on 2008 or newer, so I can't use it since I'm still running 2005), and the DELETE EXISTS, but I'm running into some trouble getting DELETE IN to work.

The problem that I'm having with DELETE IN is that my big table has a composite primary key, meaning I can only identify unique rows using all those columns together.

Is it possible in T-SQL to have multiple expressions as parameters to the 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)

Upvotes: 7

Views: 183

Answers (1)

Lamak
Lamak

Reputation: 70648

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

Upvotes: 14

Related Questions