Looping Delete

[rmz8]

I have a text file with a list of e-mail addresses that I want to be deleted from a table. How can I create a query that searches the entire database Email and deletes any records in the Email column based on that text file.

Ryan ;-]

 

[Sarky78]

You could try populating a list with the text file email addresses first. Then just use a query like this:

Delete from tbl
WHERE email_address In (#valuelist#)

the #valuelist# will be the name of the list. off the top of my head, i can't actually think of the correct syntax, but i have just tried a quick query, not using CF and that worked fine

Tony

 

[rmz8]

Could you be a bit more specific? Does anyone else know how something like this might be accomplished?

Ryan ;-]

 

[Sarky78]

Read the text file into a comma delimeted list, using CFFILE, then you have got all of the emails that you want to search the database for stored in one list.

Then using the a delete query you can delete all of the records form the database where they are in the list that you have created.

The query would look something like this

Delete from Table
WHERE email IN (#email_list#)

there would be no need to loop over the same query all the time, this would take care of it in one pass !

 

[Guest_imported]

Your on the right line with these posts. I do stuff like this from time to time in data conversion. Just use CFFILE to read in the file to a variable. Lets call it 'varFile'.

Then you can loop like this:

<CFLOOP INDEX=&quot;thisLine&quot; LIST=&quot;#varFile#&quot; DELIMITERS=&quot;#chr(13)#&quot;>
<CFQUERY NAME=&quot;DeleteEmail&quot; DATASOURCE=&quot;yourDS&quot;>
DELETE FROM Email_TABLE WHERE Email_COLUMN = '#Trim(thisLine)#';
</CFQUERY>
</CFLOOP>

I might suggest instead of deleting these values write away, store them in a seperate table, then delete them from a query on that table and you should also check for errors while mass-converting or mass-deleting files so that you know what worked and what didn't.

-Steve
sparks@houston.rr.com