REMOVE WITH INTEREST

Question

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

The most immediate way I thought was to make an INNER JOIN and solve my problem. But the write conditions for all columns (worry about NULL ) are not elegant (maybe my tables are not elegant either).

I want to use INTERSECT . I do not know how to do that? 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 have been removed from the @A table.

This led me to the second question: why does the DELETE @A FROM @B command delete all rows from the @A table?

Answer 1

Try the following:

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

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

This is based on a Paul White blog post using INTERSECT to check for inequality.

SQL Fiddle

Answer 2

To answer your first question, you can remove it based on join :

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

The second case is really strange. I remember a similar case and many complaints about this behavior. I will try to catch this question.

Answer 3

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

Regarding the question of why all the lines were deleted, this is because there is no limit condition. Your FROM gets a table to process, but there is no WHERE to prevent certain rows from being deleted from @A .

Answer 4

• Create a table (T) defining primary keys
• insert all entries from A to T (I assume there are no duplicates in)
• try pasting all entries from B to T 3A. if the insert failed, delete it from B (already exists)
• Drop T (you really shouldn't !!!)

Answer 5

Giorgi's answer explicitly compares all the columns you wanted to avoid. You can write code that does not explicitly list all columns. EXCEPT creates the necessary result set, but I do not know how to use this result set to DELETE source rows from A without a primary key. Thus, the solution below saves this intermediate result in a temporary table using SELECT * INTO . Then removes everything from A and copies the temporary result to 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 instead of a complete list of columns, it uses * . Of course, you can also explicitly specify all columns. It will still be easier to write and process than to write comparisons of all columns and take care of possible NULLs.