Removing a row when compared to another database or text file.

[Frank Maestas]

Hello,
I have a company MySQL database containing e-mail addresses. Some of
these addresses are bad. I have a text file with the addresses I need
to remove from the MySQL database.

Is there a way to compare the database against my text file, then when
an address matches, remove the row from the database?

Should I imnport the text file to new database then do the compare?
What I'm stuck on is how to execute a compare then remove the row if a
match is found. (Still a little new to MySQL). A point in the right
direction needed.

Thanks!
Frank Maestas

[Aggro]

Frank Maestas wrote:

Is there a way to compare the database against my text file, then when
an address matches, remove the row from the database?

Easiest way that comes to my mind is something like this:

You have a text file that looks like this:
-----------------
ad*****@domain.com.invalid
ad******@domain.com.invalid
ad******@domain.com.invalid
-----------------

And you have a table that looks perhaps like this:

| mytable |
-----------
| id |
| name |
| email |
-----------

You could use a text editor (a little more advanced than notepad) to
modify your text file to look something like this:

----------------
delete from mytable where email = 'ad*****@domain.com.invalid';
delete from mytable where email = 'ad******@domain.com.invalid';
delete from mytable where email = 'ad******@domain.com.invalid';
----------------

With a modern text editor you should be able to for example find and
replace line change with

';\ndelete from mytable where email = '

And then manually fix the first and last line.

Then when you got the file ready, you can use for example mysql console
program to execute sql-statements:

mysql -u root -p databasename < yourfile.txt

Since you are deleting stuff from the database, I advice to take a
backup from database before giving commands that will delete rows. (Of
course this should be obvious, but I am telling it anyway, because so
many people don't take backups and then they cry about the lost data...)