• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Bulk changing user group query

#1
Hi,

Was wondering if anyone could help...

I need to give access to a locked down forum for about 1000 users, I've created a group and set the permissions, changing their primary group to this one works as I'd like it to (for some reason adding it as secondary doesn't seem to, but I'm not too worried about that as we don't plan on adding any more forums/groups anyway). I just don't want to have to do it by hand!

So I'd like to make a MySQL query that compares existing users' email addresses to a list I have and if they match change their primary group, but I'm struggling. Does anyone know how I might be able to do this? Or is there any mod that would help?

Thanks in advance,
Brendan
 

Chris D

XenForo developer
Staff member
#2
Something like this should work:

Code:
UPDATE xf_user
SET user_group_id = '999'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com')
Don't forget to change the user_group_id :p
 
#6
Something like this should work:

Code:
UPDATE xf_user
SET user_group_id = '999'
WHERE email IN ('email1@gmail.com', 'email2@gmail.com')
Don't forget to change the user_group_id :p
Hi Chris! Is there a way to add a secondary user group using this method without changing the primary group?

Thanks!
 
#10
One more question! WHen I update, I need to either:
1. add a group without removing groups from a user, or...
2. add multiple groups at once

I tried:
Code:
UPDATE xf_user
SET user_group_id = ('5','3','6','7')
WHERE email IN ('xxxxx@gmail.com')
With the error, "#1241 - Operand should contain 1 column(s)"

What am I doing wrong?

Thank you!
 

wang

Well-known member
#11
One more question! WHen I update, I need to either:
1. add a group without removing groups from a user, or...
2. add multiple groups at once

I tried:
Code:
UPDATE xf_user
SET user_group_id = ('5','3','6','7')
WHERE email IN ('xxxxx@gmail.com')
With the error, "#1241 - Operand should contain 1 column(s)"

What am I doing wrong?

Thank you!
You should use the IN operand, which allows you to specify multiple values in a WHERE clause. The operand = is to add a single value. You must remove the brackets from the group ids.

Something like this should work.

Code:
UPDATE xf_user SET  secondary_group_ids =  '5, 3, 6, 7' WHERE email = 'email@gmail.com';
 
Last edited:
#12
You should use the IN operand, which allows you to specify multiple values in a WHERE clause. The operand = is to add a single value. You must remove the brackets from the group ids.

Something like this should work.

Code:
UPDATE xf_user SET  secondary_group_ids =  '5, 3, 6, 7' WHERE email = 'email@gmail.com';
Hi there! Returning to this SQL query because I wanted to use it again. Question, will this remove other secondary groups from the user? If I set '6' for example, and they are already '5' will '5' be removed? If so, is there any way to prevent this?
 
#13
Also, running this SQL Query:

Code:
UPDATE xf_user SET  secondary_group_ids =  '6' WHERE email = 'email1@gmail.com' 'email2@gmail.com' 'email3@gmail.com' 'email4@gmail.com' 'email5@gmail.com';
I get:

Code:
 0 rows affected. (Query took 0.0065 sec)
And I know for a number of the email addresses '6' was not set. What am I doing wrong.

THANKS in advance for your support!

M