email query

[RichieBoy67]

I am able to do a query to delete users with a certain address but what I want to do is just remove the email address so they do not bounce. This is simple but I would like to do a few other things as well to clear out my user email addresses that are not active.

#1 - Remove certain address from users but keep users. For example remove all email addresses that contain .xmail.com

#2 - Take those users with that address and move them to a different usegroup.

I have something like this

PHP Code:

UPDATE
user
SET
usergroupid = X
WHERE
  email
LIKE '%.xmail.com' 


I have not finished though because I need to remove the address as well. Is this right and what do I add for the delete part just to delete the address and not the entire user?

Thanks my Vbulletin family!

[ForceHSS]

PHP Code:

UPDATE `user` SET `usergroupid`= x WHERE `email` LIKE '%.xmail.com' 


this will change usergroupid to what you set x to

[Lynne]

Just a note to say you should always test queries on a test site first and before doing so on the live site make sure to make a database backup.

[RichieBoy67]

Originally Posted by ForceHSS

PHP Code:

UPDATE `user` SET `usergroupid`= x WHERE `email` LIKE '%.xmail.com' 


this will change usergroupid to what you set x to

Yeah, that is only the first part of the query. Not only do I want to change the usergroup of those with certain email addresses but i want to remove those addresses from the user accounts. This is the part of the query I am having trouble with.

Thanks for the reply my friend!

--------------- Added [DATE]1418235303[/DATE] at [TIME]1418235303[/TIME] ---------------

Originally Posted by Lynne

Just a note to say you should always test queries on a test site first and before doing so on the live site make sure to make a database backup.

Thanks Lynn, I will make a back up before hand but I am not going to run anything until I am sure it is what I want.

[Dave]

You want to remove the address as in setting the email column to an empty value? Or do you want to remove a specific part of the email address?

[RichieBoy67]

The entire address. I do not want to lose those members but I do not want those addresses to receive any type of email so I just want to remove the entire address from the user accounts.

Thanks dave

[Dave]

It's a matter of changing the email column to an empty string:

PHP Code:

UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com' 


When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

[ForceHSS]

Originally Posted by Dave

It's a matter of changing the email column to an empty string:

PHP Code:

UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com' 


When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

tested that myself as I had a feeling it would not work
after you have changed usergroupid try this
UPDATE `user` SET `email`='' WHERE `usergroupid`= x

[RichieBoy67]

Originally Posted by Dave

It's a matter of changing the email column to an empty string:

PHP Code:

UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com' 


When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

yeah, I tried this early but it would not work for me though I could have had it wrong. Thanks much

--------------- Added 10 Dec 2014 at 14:12 ---------------

Originally Posted by ForceHSS

tested that myself as I had a feeling it would not work
after you have changed usergroupid try this
UPDATE `user` SET `email`='' WHERE `usergroupid`= x

That makes sense to do them separately.

Thanks guys!!

[ForceHSS]

Happy to help