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:356200
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
