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.