Query to delete almost-duplicate records from database?

[GWH]

I've got a table in SQL Server 7 that has almost-duplicate records: they're identical except for a keynumber. I don't know how they got there, but they must go! I've built a query that joins the table to itself and selects out all the records that have duplicates, and their duplicates.<br>(query edited for brevity)<br>&nbsp;&nbsp;&nbsp;&nbsp;select distinct a.* from thetable a<br>&nbsp;&nbsp;&nbsp;&nbsp;full outer join thetable b<br>&nbsp;&nbsp;&nbsp;&nbsp;on ((a.productname=b.productname) and a.Location=b.Location))<br>&nbsp;&nbsp;&nbsp;&nbsp;where a.KeyNumber &lt;&gt; b.KeyNumber<br>&nbsp;&nbsp;&nbsp;&nbsp;order by a.productname;<br><br>If I could get a result set of only the duplicates, I could use it to purge the table.&nbsp;&nbsp;Using the results of this query would purge ALL of the records that have dupes.<br><br>Any ideas on how to get this?

 

[carp]

Well, I'm an Oracle puke but I THINK this ought to work in SQLServer:<br><br>DELETE FROM thetable<br>where KeyNumber IN<br>&nbsp;&nbsp;( select distinct a.KeyNumber from thetable a<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;full outer join thetable b<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;on ((a.productname=b.productname) and a.Location=b.Location))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where a.KeyNumber &gt; b.KeyNumber);<br><br>I BELIEVE this will leave the row with the lowest KeyNumber value.&nbsp;&nbsp;I would DEFINITELY &quot;trust but verify&quot; on this one.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;