如何比较两个表并删除SQL中的重复行？ [英] How can I compare two tables and delete the duplicate rows in SQL?

问题描述

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

有没有人有例子在MSSQL服务器中我该怎么做？

解决方案

嗯，在某些时候你将要检查所有的列 - 也可以加入...

 从一个第一个表中删除一个
 $ b INNER JOIN b  - 第二个表
 ON b.ID = a.ID 
 AND b.Name = a.Name 
 AND b.Foo = a.Foo 
 AND b.Bar = a.Bar 
  

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

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?

解决方案

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.

这篇关于如何比较两个表并删除SQL中的重复行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！