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.

Answer 3

1. Create a table (T) defining the primary keys
2. insert all records from A into T (i will assume there are no duplicates in A)
3. try to insert all records from B in T 3A. if insert fails delete it from B (already exists)
4. Drop T (you really shouldn't !!!)

Answer 4

You can use Giorgi's answer to delete the rows you need.

As for the question regarding why all rows were deleted, that's because there is no limiting condition. Your FROM clause gets a table to process, but there is no WHERE clause to prevent certain rows from being deleted from @A.

Answer 5

Giorgi's answer explicitly compares all columns, which you wanted to avoid. It is possible to write code that doesn't list all columns explicitly. EXCEPT produces the result set that you need, but I don't know a good way to use this result set to DELETE original rows from A without primary key. So, the solution below saves this intermediary result in a temporary table using SELECT * INTO. Then deletes everything from A and copies temporary result into A. Wrap it in a transaction.

-- generate the final result set that we want to have and save it in a temporary table
SELECT *
INTO #t
FROM
(
    SELECT * FROM @A
    EXCEPT
    SELECT * FROM @B
) AS E;

-- copy temporary result back into A
DELETE FROM @A;

INSERT INTO @A
SELECT * FROM #t;

DROP TABLE #t;

-- check the result
SELECT * FROM @A;

result set

value    username
1        User 1
3        User 3

The good side of this solution is that it uses * instead of the full list of columns. Of course, you can list all columns explicitly as well. It will still be easier to write and handle, than writing comparisons of all columns and taking care of possible NULLs.