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.