How to delete duplicate records from Contact2 table in Goldmine ver 9

There are some duplicate records in Contact2 table. No duplicate records exist in contact1. My Goldmine version 9 database uses an SQL Server 2008 database back end. How do I remove the duplicate and triplicate records, while retaining at least one of the records for each contact in Contacts2 table.
bobox00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G GodwinDatabase AdministratorCommented:
I typically rank them by something such as date or ID for each unique record I want to keep (that would be per contact for you).  Then delete all that are not the highest rank (such as if I am keeping the last record), or those that are not the first rank (if I want to keep the first record).

Let me know if you need help with ranking them.

-G

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luan JubicaProject ManagerCommented:
Do you have an id on the table?
What are the values of the ID on the duplicated records?
Alpesh PatelAssistant ConsultantCommented:
Say for example

delete b2 from BalancedDailyFiles B1 , BalancedDailyFiles b2
where b1.BalancedFile=b2.BalancedFile and b1.FileDate = b2.FileDate and b1.InsuranceCompanyID=b2.InsuranceCompanyID
and b1.BalancedDailyFilesID > b2.BalancedDailyFilesID
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

GMGeniusSoftware Development Manager/DeveloperCommented:
how many duplicate CONTACT2 rows are we talking about here?
bobox00Author Commented:
I found that the redundant rows contained null data, in each field. so i took a backup of the database and deleted the redundant rows with the attached SQL statement:

 
SELECT distinct *
FROM contact2
where UTITLE_DEP is null
and CONTACT2.ACCOUNTNO in 
(SELECT accountno
FROM contact2
GROUP BY accountno
HAVING COUNT(*)>1)
order by CONTACT2.ACCOUNTNO

Open in new window

bobox00Author Commented:
Well that should have been...
DELETE 
FROM contact2
where UTITLE_DEP is null
and CONTACT2.ACCOUNTNO in 
(SELECT accountno
FROM contact2
GROUP BY accountno
HAVING COUNT(*)>1)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.