You Dream it...We Build it... 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>
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.
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
Type Database
Query Error You can't specify target table 'payoutinfotype' for update in FROM clause
Datasource test
Native Error Code 1093
SQL State HY000