DELETE WITH INTERSECT

Question

I have two tables with the same number of columns with no primary keys (I know, this is not my fault). Now I need to delete all rows from table A that exists in table B (they are equal, each one with 30 columns).

The most immediate way I thought is to do a INNER JOIN and solve my problem. But, write conditions for all columns (worrying about NULL) is not elegant (maybe cause my tables are not elegant either).

I want to use INTERSECT. I am not knowing how to do it? This is my first question:

I tried (SQL Fiddle):

declare @A table (value int, username varchar(20))
declare @B table (value int, username varchar(20))

insert into @A values (1, 'User 1'), (2, 'User 2'), (3, 'User 3'), (4, 'User 4')
insert into @B values (2, 'User 2'), (4, 'User 4'), (5, 'User 5')

DELETE @A 
    FROM (SELECT * FROM @A INTERSECT SELECT * from @B) A

But all rows were deleted from table @A.

This drived me to second question: why the command DELETE @A FROM @B deletes all rows from table @A?

Answer 1

Try this:

DELETE a 
FROM @A a
WHERE EXISTS (SELECT a.* INTERSECT SELECT * FROM @B)

Delete from @A where, for each record in @A, there is a match where the record in @A intersects with a record in @B.

This is based on Paul White's blog post using INTERSECT for inequality checking.

SQL Fiddle

Answer 2

To answer your first question you can delete based on join:

delete a 
from @a a
join @b b on a.value = b.value and a.username = b.username

The second case is really strange. I remember similar case here and many complaints about this behaviour. I will try to fing that question.