I had to deal with this same issue some time back...
Here's what I do.
First, I create a temporary table for the import, and do a dump into
that, straight up. I create an identity field so that every record has a
unique integer value to use later.
I run updates on the fields in that table to do a little data cleanup -
trimming off bounding quotes, extra spaces or odd invisible characters
on the ends, etc.
Then, I'll run a query to pull out just the records with duplicate emails.
<cfquery name="qryDupeList">
SELECT Email, ImportID
FROM (import table)
GROUP BY Email, ImportID
HAVING COUNT(Email) > 1
ORDER BY Email
</cfquery.
I then loop over this query, skipping the first occurance of each email
addy, and tucking the remaining matches' ImportIDs into a list.
<cfset dupeImportIDList = "">
<cfset curEmail = "">
<cfloop query="qryDupeList">
<cfif Email IS NOT curEmail>
<cfset curEmail = Email>
<cfelse>
<cfset dupeImportIDList = ListAppend(dupeImportIDList, ImportID)>
</cfif>
</cfloop>
If there are any dupes, I just delete all records with those ImportIDs.
<cfif ListLen(dupeImportIDList)>
<cfquery>
DELETE FROM (import table)
WHERE ImportID IN (#dupeImportIDList#)
</cfquery>
</cfif>
The only real trick with this is, the duplicate records deleted are
kinda random. So if you have other data associated with those email
addresses, and that data varies from record to record, you're basically
flipping coins to see which one you end up with.
You could choose to display the data on a page for the user to pick and
choose which record out of the duped records they want to keep, naturally.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305949
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
