SQL Server query delete results of a join from a table

Question

I'm not a SQL Server expert and I'm struggling with this query. Can anyone help?

DELETE
FROM PPTMAILLISTC.dbo.emailTables
WHERE  email IN (SELECT *
FROM PPTMAILLISTC.dbo.emailTables tab1
    INNER JOIN PPTMAILLISTAB.dbo.emailTables tab2
        ON tab1.email = tab2.email)

SQL Server Management Studio returns.

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Basically, there are 2 separate tables both called the same (dbo.emailTables) in 2 separate databases (PPTMAILLISTC and PPTMAILLISTAB).

Where the both databases have the same results (which I can find out using the join i.e.)

SELECT *
FROM PPTMAILLISTC.dbo.emailTables tab1
    INNER JOIN PPTMAILLISTAB.dbo.emailTables tab2
        ON tab1.email = tab2.email

I want to delete the results of this join from PPTMAILLISTC.dbo.emailTables.

score 1 · Answer 1 · answered Sep 04 '13 at 14:59

1

You can get rid of the use of IN and just use your inner SELECT statement and convert it to a DELETE and just reference the ALIAS (tab1) of the table you actually want to affect like this:

DELETE  tab1
FROM    PPTMAILLISTC.dbo.emailTables tab1
        INNER JOIN PPTMAILLISTAB.dbo.emailTables tab2 ON tab1.email = tab2.email

answered Sep 04 '13 at 14:59

db_brad

903
6
22

Here's another good example of this: [t-sql-selecting-rows-to-delete-via-joins](http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins) – db_brad Sep 04 '13 at 15:02
Hi db_brad! Thanks so much. Worked like a charm. This was really really good of you to take the time to help. Thanks so much!!! – Dave Sep 04 '13 at 15:06
No problem Dave! Happy to help. – db_brad Sep 04 '13 at 15:08

Gordon Linoff · Answer 2 · 2013-09-04T15:33:57.667

You need something like this:

DELETE FROM PPTMAILLISTC.dbo.emailTables
    WHERE email IN (SELECT tab1.email
                    FROM PPTMAILLISTC.dbo.emailTables tab1 INNER JOIN
                         PPTMAILLISTAB.dbo.emailTables tab2
                         ON tab1.email = tab2.email
                   );

Even if both tables have only one column, the * resolves to two columns tab1.email and tab2.email. As the error message says, the select list for an in subquery can have only one column.

EDIT:

This is actually simpler to write as:

DELETE FROM PPTMAILLISTC.dbo.emailTables
    WHERE email IN (SELECT tab2.email
                    FROM PPTMAILLISTAB.dbo.emailTables tab2
                   );

You don't need to do the join in the in subquery.

SQL Server query delete results of a join from a table

2 Answers2