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?

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

Solution 2

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

delete * from table1 intersect select * from table2

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

Comments

[Jonathan Leffler]

This works nicely as long as none of the columns contains nulls. As soon as that happens, you have to start messing with complex conditions like (a.Name = b.Name OR (a.Name IS NULL AND b.Name IS NULL)) for each nullable column. Another reason to shun nulls.

[MAK]

@Marc Gravell, If table a and table b present in the view. Then how can I delete the duplicate rows and keep original once? I have posted [stackoverflow.com/questions/32065340/… on such situation.

[rpisryan]

Using INTERSECT and EXCEPT (see other answer) for these types of operations avoids the NULL problem sqlblog.com/blogs/paul_white/archive/2011/06/22/…