This is some code I wrote yonks ago that deleted duplicates from a DB of
Jack Handey Deep Thoughts compiled from a stack of web pages. Not elegant,
but it works.
=======================
<CFPARAM NAME="NumberOfDupes" DEFAULT="0">
<!--- Start the CFLOOP which goes through every record in turn --->
<CFLOOP INDEX="LoopCount" FROM="1" TO="3000">
<!--- Get the quote at the LoopCount value --->
<CFQUERY DATASOURCE="deepthoughts" NAME="GetQuote">
SELECT QuoteId,
Quote
FROM Quotes
WHERE QuoteId = #LoopCount#
</CFQUERY>
<!--- Sets a variable to the entire quotation --->
<CFSET TheQuote = GetQuote.Quote>
<!--- If it's a record that exists ... --->
<CFIF TheQuote IS NOT "">
<!--- ... find all the records in the DB (other than this one) that have
a Quote value that matches the variable we just set --->
<CFQUERY DATASOURCE="deepthoughts" NAME="GetDupes">
SELECT QuoteId
FROM Quotes
WHERE QuoteId <> #LoopCount#
AND Quote LIKE '#TheQuote#'
</CFQUERY>
<!--- If there are any duplicates ... --->
<CFIF GetDupes.QuoteId IS NOT ''>
<!--- ... delete them --->
<CFOUTPUT QUERY="GetDupes">
<CFSET NumberOfDupes = NumberOfDupes + 1>
<CFQUERY DATASOURCE="deepthoughts">
DELETE FROM Quotes
WHERE QuoteId = #QuoteId#
</CFQUERY>
</CFOUTPUT>
</CFIF>
</CFIF>
</CFLOOP>
<P>Job done.</P>
<P><CFOUTPUT>#NumberOfDupes# duplicates were deleted</CFOUTPUT></P>
=============================
--
Aidan Whitehall <[EMAIL PROTECTED]>
Netshopper UK Ltd
Advanced Web Solutions & Services
http://www.netshopperuk.com/
Telephone +44 (01744) 648650
Fax +44 (01744) 648651
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
