CODEWITHSUNDEEP
sqlsql-server-2005
Dave
Dave

Reputation: 139

SQL Server query delete results of a join from a table

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.

Upvotes: 0

Views: 1066

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 0

db_brad
db_brad

Reputation: 923

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

Upvotes: 1

Related Questions