删除相交 [英] DELETE WITH INTERSECT

问题描述

我有两个列数相同但没有主键的表(我知道，这不是我的错).现在我需要删除表 B 中存在的表 A 中的所有行(它们是相等的，每行有 30 列).

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

我认为最直接的方法是做一个 INNER JOIN 并解决我的问题.但是，为所有列编写条件(担心 NULL)并不优雅(可能是因为我的表也不优雅).

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

我想使用INTERSECT.我不知道该怎么做?这是我的第一个问题:

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

我试过(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

但是所有行都从表 @A 中删除了.

But all rows were deleted from table @A.

这让我想到了第二个问题:为什么命令 DELETE @A FROM @B 会删除表 @A 中的所有行?

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

推荐答案

试试这个:

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

从@A 中删除，对于@A 中的每条记录，@A 中的记录与@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.

这是基于 Paul White 的 博客文章 使用 INTERSECT 进行不等式检查.

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

SQL 小提琴

这篇关于删除相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！