6

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?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
zSynopsis
  • 4,854
  • 21
  • 69
  • 106

5 Answers5

9

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 5
    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. – Jonathan Leffler Mar 02 '09 at 00:39
  • @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 [http://stackoverflow.com/questions/32065340/sql-server-2008-r2-delete-duplicate-rows-from-tables-containing-in-view/32065972?noredirect=1#comment52032907_32065972] on such situation. – MAK Aug 18 '15 at 10:38
  • Using INTERSECT and EXCEPT (see other answer) for these types of operations avoids the NULL problem http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx – rpggio May 04 '16 at 15:15
8

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

delete * from table1 intersect select * from table2
Chris Van Opstal
  • 36,423
  • 9
  • 73
  • 90
1

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

Rich Andrews
  • 4,168
  • 3
  • 35
  • 48
0

try this:

DELETE t1 FROM t1 INNER JOIN t2 ON t1.name = t2.name WHERE t1.id = t2.id
Leebeedev
  • 2,126
  • 22
  • 31
0

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.

Gthompson83
  • 1,099
  • 1
  • 8
  • 18