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?

Nature

If i remember well, doing DELETE A FROM B does not restrict anything, and this normal that it deletes everything. you could restrict by doing something like : DELETE A FROM B where A.value = B.Value Adding a FROM after a delete is kind like of a LEFT JOIN – Civic

You meant DELETE A and that does not run. Right now you are deleting @A CROSS JOIN (something else). Which deletes everything if there is at least one row in something else. Look at the query plan to see this. – Agley

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

Barbrabarbuda

Thanks a lot. That is the syntax I was looking for. It is hard to choose the right answer since I asked two questions. But the best answer for my second question is on comments given by @usr. – Nature

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.

Dinge

Thanks a lot. It works but I am wanting to avoid the join because the number of columns in my real case. – Nature

Answer 3

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.

Terrain

Having a table to process isn't sufficient sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/1896 – Frontwards

Also consider using EXCEPT instead of INTERSECT if your ultimate goal is to delete from only one of the two tables being compared. The logic and steps would be simpler. – Persse

@MartinSmith - in your sample @A is emptied (deleted...). It's true that if the FROM clause is empty, nothing is deleted, but as for what happened in the OP (where the FROM clause ISN'T empty...), the reason is as I stated. – Terrain

Answer 4

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 !!!)

Baughman

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.

Silicone