Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Select Query to ouput to a new table


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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc