如何比较两个表并删除SQL中的重复行？

I have two tables and I need to remove rows from the first table if an exact copy of a row exists in the second table.

我有两个表，如果第二个表中存在一行的精确副本，我需要从第一个表中删除行。

Does anyone have an example of how I would go about doing this in MSSQL server?

有没有人有一个如何在MSSQL服务器中执行此操作的示例？

5 个解决方案

#1

Well, at some point you're going to have to check all the columns - might as well get joining...

那么，在某些时候你将不得不检查所有列 - 也许还要加入......

DELETE a
FROM a  -- first table
INNER JOIN b -- second table
      ON b.ID = a.ID
      AND b.Name = a.Name
      AND b.Foo = a.Foo
      AND b.Bar = a.Bar

That should do it... there is also CHECKSUM(*), but this only helps - you'd still need to check the actual values to preclude hash-conflicts.

应该这样做......还有CHECKSUM（*），但这只有帮助 - 您仍然需要检查实际值以排除哈希冲突。

#2

If you're using SQL Server 2005, you can use intersect:

如果您使用的是SQL Server 2005，则可以使用intersect：

delete * from table1 intersect select * from table2

#3

I think the psuedocode below would do it..

我认为下面的伪代码会这样做..

DELETE FirstTable, SecondTable
FROM FirstTable
FULL OUTER JOIN SecondTable
ON FirstTable.Field1 = SecondTable.Field1
... continue for all fields
WHERE FirstTable.Field1 IS NOT NULL
AND SecondTable.Field1 IS NOT NULL

Chris's INTERSECT post is far more elegant though and I'll use that in future instead of writing out all of the outer join criteria :)

克里斯的INTERSECT帖子更加优雅，我将来会用它而不是写出所有的外连接标准:)

#4

I would try a DISTINCT query and do a union of the two tables.

我会尝试DISTINCT查询并执行两个表的并集。

You can use a scripting language like asp/php to format the output into a series of insert statements to rebuild the table the resulting unique data.

您可以使用像asp / php这样的脚本语言将输出格式化为一系列插入语句，以重建表生成的唯一数据。

#5

try this:

尝试这个：

DELETE t1 FROM t1 INNER JOIN t2 ON t1.name = t2.name WHERE t1.id = t2.id

#1