Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list of invalid email addresses parsed in the following format;

enter image description here

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?

share|improve this question
What is the logic you use to decide between valid and invalid email addresses? – Vérace Jun 15 '14 at 5:36
Not sure if I've understood your requirements exactly, but this might be of interest - MySQL regexps for email. – Vérace Jul 20 '14 at 7:49

If you have a main table like this

    email VARCHAR(255),
    PRIMARY KEY (id)

You can built the table that contains the ids of the users with bad email addresses

CREATE TABLE users_invalid_emails
SELECT FROM users WHERE email LIKE 'email%' and email LIKE '';
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 FROM my_BBusers WHERE email LIKE 'email%' and email LIKE '';
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 !!!

share|improve this answer
As I'm new to mySQL and direct edits, I'm not sure of your experience with myBB, but I'll give you an example of a the fields within myBB_users; How would I use your code to work with the myBB_users table? – Jake Mar 27 '14 at 16:55
@Rolando, What's the performance of delete join? Would it be as slow as delete from x where email in (....)? – Pacerier Feb 5 '15 at 22:42

In case of you are having a list of the mail adresses maybe like that '', '' etc... you could do a simple

DELETE FROM myTable WHERE email in ('', '');

This might not be the best solution, but the easiest to undersand IMHO ;) However, be careful as delete means delete in most cases as in data are gone. Also not how your application handles deleting users like that.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.