Mark,
Perhaps I'm missing something... but it seems like you already have your
list to delete. Why are you comparing again? Why not just do:
DELETE FROM someDBTable
WHERE emailaddress IN ('# todelete#')
Using cfqueryparam of course :)
Is the list too large? Sometimes the driver will only handle n number of
items in a list - something like 16k I think.
-Mark
-----Original Message-----
From: i...@markleder.com i...@markleder.com [mailto:i...@markleder.com]
Sent: Wednesday, July 17, 2013 9:02 AM
To: cf-talk
Subject: Compare Two Lists
Hi all,
Attempting to clean up a table with email records. In addition to the
existing table of good and bad email addresses, I have a csv file with
addresses that would need to be matched up to the db table, then deleted (so
I just have good addresses).
Email addresses are properly formatted in both the db and in the csv, so I
won't have to deal with duplicate addresses or hang ups because of improper
formatting. The db table has about 10,000 records. The csv file has about
4,000 addresses that will need to be matched and then dropped from the db
table. Each recordset only has email addresses, so we're only dealing with
one column.
I've been working with the following, but I'm not sure this is the best way
to go about it, or if it will function properly. And, I'm not sure that the
ListGetAt or the delete "WHERE" clause will work. Conceptually for some
reason I have a mental block about arrays.
Thanks for your advise. Mark
========================
<!--- Get the existing db email addresses --->
<cfquery name="getTableAddresses" datasource="#dsnSQL#" username="#dsnUID#"
password="#dsnPWD#">
SELECT emailaddress
FROM someDBTable
ORDER BY emailaddress asc
</cfquery>
<!--- Get the addresses which are bad from the csv file --->
<cfhttp
name="cleanedAddressestoDelete"
method="get"
url="http://www.somesite.com/file.csv";
/>
<cfset oldList = ValueList(getTableAddresses.emailaddress)>
<cfset todelete = ValueList(cleanedAddressestoDelete.emailaddress)>
<cfloop From="1" To="#ListLen(oldList)#" index="emailaddress">
<cfif ListGetAt(oldList, emailaddress) EQ
ListGetAt(todelete,emailaddress)>
<cfquery name="deleteAddress" datasource="#dsnSQL#" username="#dsnUID#"
password="#dsnPWD#">
DELETE FROM someDBTable
WHERE emailaddress = "#todelete.emailaddress#"
</cfquery>
</cfif>
</cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356203
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
