Thread: Load using infile, but skip if in another table

Here's the scenario... I'm working in a TM department and I have the Federal DNC list for our calling areas loaded in a table called 'ftcdnc' in my database. I have a huge (2gb+) list of leads that I want to load into the database from a CSV file, but I want to skip any leads that have a matching phone number in the ftcdnc table.

I realize this process will require close to 100% of the system resources, but I only need to do it once to strip out the DNC numbers. Then I can export the scrubbed list for use in Vicidial.

How would I do that? I have my dnc list loaded and leads loaded in their tables. I could make a PHP page that loops through every lead record, but surely there's an easier way through the mysql console...

Well, I haven't deleted anything as yet, but I did try:

Code:

SELECT COUNT(*) FROM leads l,ftcdnc f WHERE l.phone NOT IN(f.phone);

to see if it would return the total records that DIDN'T match to the DNC list and it froze the machine (over 10m DNC numbers comparing to 8m leads). I waited about 10 minutes before I forced the mysql console to close so the people who needed to access the DB could... Would that be the proper syntax for what you're suggesting?

Code:

DELETE FROM leads WHERE phone IN(ftcdnc.phone);

On an unrelated note... Good GOD, this company needs to invest in a real server for this and not use a Dell POS with 256mb of ram... I'm sorely tempted to do this at home where I have a REAL rig. But I do get paid hourly, so I guess it's better for me in the end.