How to delete lists of email addresses from mysql databases?
I have a list of invalid email addresses parsed in the following format;
There are roughly 1500 invalid addresses, and I need to remove them from my myBB users.
What SQL query can I run to remove the list from my database?
If you have a main table like this CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(255),
....
PRIMARY KEY (id)
);
You can built the table that contains the ids of the users with bad lists of email addresses
CREATE TABLE users_invalid_emails
SELECT id.email FROM users WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE users_invalid_emails ADD PRIMARY KEY (id);
Look over the content of users_invalid_emails. If the emails in that table are to be deleted, you can now do a DELETE JOIN:
DELETE A.* FROM users A INNER JOIN users_invalid_emails B USING (id);
Before you do this, make sure you have a backup of the table.
In your case, the code will be
CREATE TABLE myBBusers_invalid_emails
SELECT id.email FROM my_BBusers WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE myBBusers_invalid_emails ADD PRIMARY KEY (id);
DELETE A.* FROM my_BBusers A INNER JOIN myBBusers_invalid_emails B USING (id);
DROP TABLE myBBusers_invalid_emails;
Give it a Try !!!