How can I compare two tables and delete the duplicate rows in SQL?

Question

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?

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

Answer 2

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

Answer 3

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

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.

Answer 4

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

delete * from table1 intersect select * from table2