How to compare two tables and remove duplicate rows in SQL?

Question

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

Does anyone have an example of how I will do this on an MSSQL server?

Answer 1

Well, at some point you will have to check all the columns - maybe also join ...

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 

This should do it ... there is also CHECKSUM(*) , but it only helps - you still need to check the actual values ​​to eliminate hash conflicts.

Answer 2

If you are using SQL Server 2005, you can use intersect :

 delete * from table1 intersect select * from table2 

Answer 3

I think the psuedocode below would do this.

 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 

The Chris INTERSECT post is much more elegant, although I will use this in the future instead of writing down all the external join criteria :)

Answer 4

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

You can use a scripting language such as asp / php to format the output to a number of insert statements to rebuild the table as a result of unique data.

Answer 5

try the following:

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