Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)
like image 884
ivanmp Avatar asked Mar 12 '12 14:03

ivanmp


People also ask

Can we have 2 conditions in for loop?

It do says that only one condition is allowed in a for loop, however you can add multiple conditions in for loop by using logical operators to connect them.

How do you evaluate multiple expressions in one line?

We can use the , or comma operator to evaluate multiple expressions in one line. It evaluates from left-to-right and returns the value of the last item on the right or the last operand. If you log the value of x it would be 27.

Can you have multiple && in an if statement?

you can use as many && and || as you want. i've seen as many as 27 conditions all ANDed and ORed together.

Can a for loop have two conditions C++?

While Loops with multiple conditionsYou can chain together multiple conditions together with logical operators in C++ such as && (And operator) and || (Or operator).


1 Answers

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
like image 177
Lamak Avatar answered Oct 02 '22 21:10

Lamak