How to delete a list of emails from a big database.

[matar84]

I have the following table with approximately 20,000 entries.

############################
# ID # Email # Date #
############################
# 1 # jondoe@widget.com # mm-dd-yy #
# 2 # bill@aol.com # mm-dd-yy #
############################

I also have a text file containing one email address per line (about 900 lines) that need to be REMOVED from above database.

How can I write a script or use some kind of MySQL command to automatically delete the ENTIRE row wherever an email from my text file is found anywhere in the database? Remember, the database is BIG.

Your help is greatly appreciated.

[sylow]

900 items is not much and 20,000 entries is nothing also( email field is indexed I hope)

Anyway read few emails addresses into array and implode them with comma and form this query string.

DELETE FROM bigTable
WHERE Email IN ('email1@hotmail.com', 'email2@hotmail.com',...)

[Chris82]

In MySQL v4 it should be possible to use:

First, get the text file into a new temp table

-- create temp table
CREATE TABLE mytemp (emails VARCHAR(100));

-- insert the emails from the file
LOAD DATA 'emails.txt' 
   INTO TABLE mytemp;

-- get all emails which are both present in the original table and the temp table
SELECT * FROM emails
WHERE email IN (
   SELECT email FROM mytemp
);

With MySQL v3 this could work:

-- insert the emails from the file
LOAD DATA 'emails.txt' 
   INTO TABLE emails (email);

-- get all emails which are both present in the original table and the temp table
CREATE TABLE newtable
AS 
SELECT ID, DISTINCT Email, `Date` FROM emails;