Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Remove one list of emails from another?

Posted on 2003-02-28
2
Medium Priority
?
178 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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you are a budding entrepreneur and using Facebook for the same purpose, you need to make good use of the available data to gauge the impacts of your marketing campaigns. This is where important metrics come into the picture. These help you optimi…
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 PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses

572 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