?
Solved

Remove one list of emails from another?

Posted on 2003-02-28
2
Medium Priority
?
176 Views
Last Modified: 2012-05-04
We have a master email list (LIST A) of 250K+ emails.  We also have a smaller list (LIST B) of 18K emails from people who have opted-out of our mailing list.  If an email from LIST B is found in LIST A, we need to delete it from LIST A.

What is the best method for this?  I tried incorporating this into a MySQL database and running DELETE queries for all 18K records, but that ended up taking nearly 2 days of processing time.  Any suggestions?  Is Microsoft Access able to automate something like this?  If so, how? (I'm not familiar with the software)

Thank you!
0
Comment
Question by:spryder9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Accepted Solution

by:
chriscaputo earned 150 total points
ID: 8044102
The way I just did this for a client is kind of like you mentioned.  I imported both into MySQL, then wrote an ASP script to select 1000 or so at a time.  Looping through each row in the recordset, I did DELETE FROM table1 WHERE email = <emailfromtable2>.. Takes about 15-20 minutes.

i.e.

qryDupEmails = "select top 1000 email from dupTable"
rsDupEmails = objDBConn.Execute(qryDupEmails)

Do While not rsDupEmails.EOF
  qryRemove1 = "delete from masterlist where email = '" & rsDupEmails.Fields("email") & "'"
  qryRemove2 = "delete from dupTable where email = '" & rsDupEmails.Fields("email") & "'"
  ' removes from master list
  objDBConn.execute(qryRemove1)
  ' removes from dup list, so on next top1000 we don't get it again
  objDBConn.execute(qryRemove2)
  rsDupEmails.MoveNext
Loop

Hope that helps!

0
 

Author Comment

by:spryder9
ID: 8060190
Thanks - this is what I do already, but MySQL takes 6-8 seconds to process a DELETE query when the table has 200K+ entries.  Is there a better way to create the table, that will speed up this process?  Right now its just id (auto-increment, Integer) and email (char(255)).  
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read this article and get to know some best tips for outsourcing client PPC work to a white label PPC agency.
We aren’t perfect, just like everyone else.  Check out the email errors our community caught and learn the top errors every email marketer should avoid.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question