Removing Duplicate Records in a Database
SQL provides many ways of grouping data. It also has many ways to select data. But if you've ever tried to remove duplicates from a database you might have needed 2 or 3 trips to the liquor store to figure it out. I've seen routines that match and compare and order and update and delete willy nilly - all just to find and fix duplicate rows in a database. I've never had to do this to any of my databases of course, because all my constraints and applications are perfect (gah!). Still, for those of you with imperfect databases I'm happy to report there is a magic bullet. Here's the big secret.
Self Joining
Unless you are Narcissus it's not something you think about often, but it is possible to join a table with itself. This trick can be used to weed out duplicates - as in the query below.
SELECT distinct a.user_id
FROM users a, users b
WHERE a.user_id > b.user_id
AND a.email = b.email
ORDER BY a.user_id
</cfquery>
| userid | |
| 100 | myemail@myemail.com |
| 135 | myemail@myemail.com |
| 210 | myemail@myemail.com |
You can easily use this to your advantage. For example, you could delete the duplicates from the table.
DELETE FROM users
WHERE user_id IN
(SELECT distinct a.user_id
FROM users a, users b
WHERE a.user_id > b.user_id
AND a.email = b.email)
</cfquery>
10 Comments
-
Very clever.
I think people (maybe just me) sometimes forget how powerful sql is. I'm sure most people (maybe just me) would take the cfquery result and do some sort of loop with lots of tests and delete statements to achieve the same result. -
Antony, Yes, you and me both. I've done my share of mucking around with a utility script written in CF. Good for routines where there are a lot of variable possibilities, but this is a nice trick if it suites your purpose. -
another approach would be to create an empty table duplicating the design of that table except to hang a unique index off the email column using the "ignore duplicate key" option. sql server will silently throw away the duplicates when you pump the rows from the original table to this new table. -
Well I have a suggestion about dup records.
I mean finding dups in a field is easy, but true dup records a little harder.
What you can do is use the sql hash function to create a hash value for every row in the database (listing the rows by number of course).
Then put that has value in its own table, search on dupes on the hash value, and walla, you have your dup records.
Just a thought,
Fox -
This is great. Thanks very much. Made the process of deduping 90k records much quicker. -
you mofo, i ran this script and it deleted all duplicates...instead of deleting the 2nd or 3rd repeated record it deleted everything including the 1st appearance of the dup....!@#$%^&
Sites to Watch
Aggregators
RSS
Related Blog Entries