[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

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.
0
bobox00
Asked:
bobox00
2 Solutions
 
GDG_DBACommented:
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
0
 
luaniCommented:
Do you have an id on the table?
What are the values of the ID on the duplicated records?
0
 
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
GMGeniusCommented:
how many duplicate CONTACT2 rows are we talking about here?
0
 
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

0
 
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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now