Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
What is the logic you use to decide between valid and invalid email addresses? –  Vérace Jun 15 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 at 7:49

2 Answers 2

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

DELETE FROM myTable WHERE email in ('email1@example.com', 'email2@example.com');

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

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 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 !!!

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; puu.sh/7LAHG.png How would I use your code to work with the myBB_users table? –  Jake Mar 27 at 16:55

Your Answer

 
discard

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.