CODEWITHSUNDEEP
sql-server
MogulBomb
MogulBomb

Reputation: 185

Delete from table using multiple identifying columns in another table

I have two tables as described:

Table 1

Column A, Column B, Column C, Column D

Table 2

Column A, Column B, Column C, Column E, Column F

There is no relationship between the tables other than the data contained within them (table 2 is a temporary table). I want to delete rows from table one where they exist in table 2. However, it must be based on the combination of three columns. For example, delete in table 1 if there is a record in table two where columns A, B and C all match.

Upvotes: 2

Views: 3306

Answers (3)

Joe Farrell
Joe Farrell

Reputation: 3542

I upvoted Jiggles32's answer as it appears to do exactly what you want (assuming that you consider columns to match only when they have matching non-null values), but thought it was worth pointing out that you can also use this syntax:

delete a 
from dbo.Table1 a 
where exists
(
    select 1 
    from dbo.Table2 b 
    where
        a.ColumnA = b.ColumnA and
        a.ColumnB = b.ColumnB and
        a.ColumnC = b.ColumnC
);

EXISTS implements a semi-join, which checks for the existence of data in some other table without actually pulling in any data from same. Interestingly, when I ran Jiggles32's DELETE and the one above, I found that SQL Server generated exactly the same execution plan for both:

enter image description here

So use whichever you find easier to understand.

Upvotes: 2

ccarpenter32
ccarpenter32

Reputation: 1077

You're probably looking for an INNER JOIN DELETE.

DELETE a
FROM Table1 a
INNER JOIN Table2 b
ON a.ColumnA=b.ColumnA
AND a.ColumnB=b.ColumnB
AND a.ColumnC=b.ColumnC

(Or whatever the relationship is.)

Upvotes: 6

UmmmActually
UmmmActually

Reputation: 217

DELETE FROM [Table 1] 
WHERE [Table 1].[Column A] IN 
    (SELECT [Table 1].[Column A] 
     FROM [Table 1] t1 
     INNER JOIN [Table 2] t2 ON 
        t1.[Column A] = t2.[Column A] AND 
        t1.[Column B] = t2.[Column B] AND 
        t1.[Column C] = t2.[Column C])

Upvotes: 1

Related Questions