Solved

Find and Delete Duplicate Records

Posted on 2014-02-16
12
435 Views
Last Modified: 2014-02-16
I have a table called group_members:

group_membership_id int PK
contact_id int
group_id int

I have a problem where there has been duplication in the data where I have the following

group_membership_id            contact_id              group_id
1                                               100                         1000
2                                               100                         1001
3                                               101                         1000
4                                               101                         1001
5                                               100                         1000
6                                               101                         1000

Notice how rows 5 and 6 are dupes of 1 and 2.

I need to write a query to find all of these dupe and delete them.
0
Comment
Question by:ccleebelt
  • 7
  • 5
12 Comments
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
Maybe I am missing the obvious but how exactly are lines 5&6 duplicates of 1&2?

With this little bit of detail, you are going to have a difficult time finding unique ways to express what a duplicate is.
0
 

Author Comment

by:ccleebelt
Comment Utility
line 1 and 2 have the same contact_id, group_id pairs as 5 and 6.

Ideally, we find a way to delete 5 and 6 based on them being the same as 1 and 2.
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
Ahhh... 1&3 not 1&2
So is it correct to say that the combination of the two columns should be unique? In other words, there will never be more than one entry with contact_id=100 AND group_id=1000 ?

How are you connecting to the SQL Server? Do you have MS Access available?
How many columns doe this table have? Just the three?
How many rows does the table have?

Backup your database before you do anything...
0
 

Author Comment

by:ccleebelt
Comment Utility
Oops.  Yeah, nice catch.  

There should never be more than one row with any contact_id, group_id pair.

I connect through Management Studio

These are the only columns

About 300000 rows in the table.  Of which about 20% are dupes a such as 5 and 6 above.
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
Try this:
SELECT DISTINCT 'group_membership_id', 'contact_id', 'group_id' INTO <new_table> FROM  <your_table>

Open in new window


This will create a new table <new_table> (call it whatever you want) which will contain only the first occurrence of the duplicate entries. You can review this for correctness. If this looks good, rename the existing table (do not delete it yet) and then rename the new table to the original table name.

Be aware that the column group_membership_id may end up wrong for some records because you cannot know in what order the duplicates fall. If you are lucky the duplicates were created all at the end of the table, in which case you will be all good. You may have to doctor some of this column manually...
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
You can also use this shorter version since there are only the three columns:
SELECT DISTINCT * INTO <new_table> FROM  <your_table>

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ccleebelt
Comment Utility
I ran this and it creates a exact replica of the original table with the exact same duplicates.
0
 
LVL 11

Accepted Solution

by:
Technodweeb earned 500 total points
Comment Utility
My bad... Remove the group_membership_id field...
SELECT DISTINCT 'contact_id', 'group_id' INTO <new_table> FROM  <your_table>

Open in new window


I wrote that before your last response...
0
 

Author Comment

by:ccleebelt
Comment Utility
worked.  Thanks!
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
Shazaaaam!
0
 

Author Comment

by:ccleebelt
Comment Utility
This is interesting though....I need to get the group_membership_id column on the new table and then make it the PK.

SQL will not allow me to do that.

Any pointers?
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
You may have to remove the PK from the temp table (original that was renamed) first since the key cannot be named the same.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now