Delete based on composite key from another table

Question

I've been really reluctant to post this because I feel it's a common issue, but I can not seem to find an answer that applies to this situation... (maybe I'm just a really bad googler).

I have two identical tables(column wise, not data), table1 and table2. I wish to delete the records in table1 in which the composite key (col1, col2) exists in both tables. Here is what I have, which seems correct to me, but is throwing an error.

DELETE FROM Table1
WHERE (**Col1**, Col2) IN
(SELECT Col1, Col2
FROM Table1 a
JOIN Table2 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2)

Error:

Msg 4145, Level 15, State 1, Line 212 An expression of non-boolean type specified in a context where a condition is expected, near ','.

I have put two * around the portion of code which has 'Red error squiggles'.

Solution 1

This can be cleanly performed using JOIN with your DELETE:

DELETE a
FROM 
    Table1 a 
    JOIN Table2 b
        ON a.Col1 = b.Col1
        AND a.Col2 = b.Col2

Solution 2

Which DBMS is it? If it is in oralce then pairwise comparison should work.

DELETE FROM Table1
WHERE (Col1, Col2) IN
(SELECT Col1, Col2
 FROM Table2)

If it is SQL server then Michael's solution should work.

Solution 3

Neither of the answers cover the general case, so I'll put mine:

You must concat your fields into one field:

Ex.:

DELETE FROM Table1 
WHERE concat(Col1,'******',Col2) 
 IN (SELECT concat(a.Col1,'******',b.Col2) 
FROM Table1 a 
JOIN Table2 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2))

Comments

[Jeff]

This solution works, but I'm hoping you could explain why mine didn't work. I have seen solutions to similar problems which use the (col1, col2) syntax.

[Michael Fredrickson]

@Jeff that's valid for Oracle, but not Sql Server... they use slightly different dialects of Sql and aren't always interchangeable.

[Jeff]

That makes sense. I could have sworn I used this syntax in SQL-Server at some point, but I'm probably wrong. It's a mess trying to keep the syntax straight between SQL-Server, MySQL, and Oracle. Thanks!

[binwiederhier]

This also works in HSQLDB: delete from table1 where (col1, col2, col3) in (select col1, col2, col3 from someview123 where ...)