Need help writing an SQL query to remove bounced emails

[howarde]

Hi,

I have a list of bounced emails, and I want to change the receive admin email option to "no" for these users, by running an update query on the user table.

I understand, I can change those options with this part:

UPDATE user SET options = options - 16 WHERE options & 16 AND userid = xxxx

However... I'm having a hard time coming up with the query to cross-reference the bounced email list. I figure I can import the bounced emails to a table called, "bounce" and essentially, I want to

UPDATE user (like above)

WHERE bounce.email = user.email

however... it's not that easy (for me) - maybe there's some mysql experts that could point me in the right direction?

[kh99]

Maybe something like this:

UPDATE bounce LEFT JOIN user USING (email)
SET options = options - 16 WHERE options & 16


Maybe you could even add a column to your bounce table and set it so you know which ones matched.

[howarde]

Cool... It works, except I want to update "user" and LEFT JOIN bounce

Thanks!

[kh99]

I'm not a MySQL expert, but I think if you "left join bounce" you get a table with every user, but the columns from bounce will be blank unless there's a matching email. So you'd be resetting the options of every user (I think).

[howarde]

Actually, as I investigate further... this isn't what I want to do anyway. Changing the Admin Emails to "no" doesn't do what I want...

Basically, I have a list of all of the bounces from our database - there's a lot as we have more than 200,000 users in our database.

I want to set it so that bounces don't get subscription notifications or other emails at all anymore, as there are thousands of bounces weekly. Not sure what to do here.

[kh99]

Yeah, what you need to do is delete all the records with the matching userids from the tables subscribethread, subscribeforum, subscribeevent, subscribegroup, and subscribediscussion. But one issue is that if someone's email bounces temporarily or if they just didn't get around to updating their new email or something, doing that will erase all their subscriptions.

[howarde]

Hmm... sounds even more difficult.

What if I just remove their email addresses from the database, and limit the actions to users who have no activity in over 2 years (or something like that).

[kh99]

Yeah, removing the email is an interesting idea. If it's bad it doesn't matter, and a user can always just re-enter it. I don't know what else might happen if you just deleted the value from that field, but you could try it.

[ForceHSS]

Could cause problems leaving that field blank. Have you checked for any plugins for bouncing emails I am sure there are some around

[howarde]

UPDATE user LEFT JOIN bounce USING (email)
SET user.email = ''
WHERE bounce.email <> '';

I just ran this on a test database and it does remove the bounce emails from the user table, and leave the field blank.

Thanks for the help.

Could cause problems leaving that field blank. Have you checked for any plugins for bouncing emails I am sure there are some around

I'll wait a few days to see if anyone has any real evidence of issues with blank emails in the DB.

Thanks.

[kh99]

UPDATE user LEFT JOIN bounce USING (email)
SET user.email = ''
WHERE bounce.email <> '';

I just ran this on a test database and it does remove the bounce emails from the user table, and leave the field blank.


Let us know what happens. By the way, I don't see any problem with the query you posted above but I wanted to clarify what I mentioned before - I know that when a query starts with "UPDATE bounce LEFT JOIN user..." for instance, it sounds like you're updating the bounce table. But you can think of the "bounce LEFT JOIN user USING (email)" part as a virtual table containing fields from both, and if you then SET fields from the user table you'll be updating the user table and not bounce, even though bounce was mentioned first. And as I mentioned, the difference is that in the "bounce left join user" virtual table will contain one row for each row from bounce, whereas "user left join bounce" will have one row for each row in user (with bounce.email NULL where there was no match).

[howarde]

Yes... you are correct on the query. Works the same either way.

Thanks for the lesson.

Single table queries are easy for me, but the whole join thing makes it more complex, and I'm not writing queries every day. :)