I'm trying to clean up a database that has a lot of duplicates from an update
attempt by the previous technician.  Some rows are duplicated as much as 20
times, some only 5-10 times, etc...

So i ran this query to see duplicates.
SELECT CONTACT,
 COUNT(CONTACT) AS NumOccurrences    
FROM [contact-casey2].[dbo].[CONTACT1]
         INNER JOIN
     [contact-casey2].[dbo].[GMIDS] ON CONTACT1.ACCOUNTNO = GMIDS.GOLDMINEID
GROUP BY CONTACT
HAVING ( COUNT(CONTACT) > 1 )

I want to keep just one copy of the row (the one where accountNo =
goldmineid).
All other duplicates can be deleted except where contact = null.

Sounds tricky without seeing the actual data, but hopefully someone can
understand what i mean.  Any help is appreciated.

You need to include table schema and some explantion on that . As of
your query , you have  more than one row matching one row (or multiple
rows ) in GMIDS .
Assuming contactid as PK in contact table ( retain the maximum or
minimum contact id ) and delete others .

SELECT CONTACT,
  MAX(CONTATCTID) AS CONTATCTID INTO #temp
 FROM [contact-casey2].[dbo].[CONTACT1]
          INNER JOIN
      [contact-casey2].[dbo].[GMIDS] ON CONTACT1.ACCOUNTNO =
GMIDS.GOLDMINEID
AND CONTACT >''
 GROUP BY CONTACT

DELETE FROM CONTACT
WHERE NOT EXISTS  (SELECT 1 FROM #temp b WHERE  CONTACT.CONTATCTID  =
b.CONTATCTID
                                   AND CONTACT.CONTACT=b.CONTACT)
AND CONTACT.CONTACT >'' -- ( DO NOT DELETE NULLS )

I suggest copying contact table to a new table and try delete on new
table and check

M A Srinivas

On Mar 10, 1:36 am, Vladimir Cepeda <Vladimir