[Guide] How to mass move "Secondary" usergroup to "Primary" via SQL.

[Mysticales]

Hey all, I googled, searched on forums here, only had found commands and internal vB functions to mass move users from one primary usergroup to another primary usergroup.

Did not find anything on how to mass move secondary (additional) usergroups to primary, except for some faint references that talk about if the user has more then 1 secondary that the normal sql query wouldnt work right.

Anyways, I just moved over 1000 users some mixed etc. But this is the template I used on 3.5.4 using phpadmin to type them in. (You should be able to use vB AdminCP SQL query too since its just 1 command at a time)

Ok here is the code, so least it can be here for someone else who may not know and just trying to muddle along like I did.

Get a SQL Query box up, if your in phpadmin select your database, user, and browse if you wanna feel safer.

UPDATE user 
SET usergroupid = 'X' 
WHERE membergroupids = 'X'
AND usergroupid = 'Y'

X = The id of the secondary group your users are currently in.
Y = The primary usergroup those users are in currently. (Before the switchover)
(Example - You have a paid subscription usergroup, you orig configured it to be a "additional" flag added but now you just wish them to be in a primary group instead of being "Registered User" Primary + "Paid Subscriber" as additional.)

The 1st query will move all the users who match the profile of just being a "Paid Subscriber" as secondary, and just a "Registered user" as primary.
(Note, if your users have multiple secondary usergroups, I will cover that toward the end)

Next, you will have done something! However now your users are in BOTH Primary AND Secondary for that same usergroup, which isnt advised by vB. So right after that, you type this in the query box.

UPDATE user 
SET membergroupids = '' 
WHERE membergroupids = 'X'
AND usergroupid = 'X'

X = The secondary group you converted above to primary group.

This here will "uncheck" the secondary usergroups flag. Since you had just moved them to Primary.

Now here is where its different.
Say you have a user or multiple users with another secondary group. (Maybe because you have a store option to buy them, have public groups for them to join, whatever)
You will need to use this code. (Providing they only have 2 secondary groups and the Primary group is the same, Like "Registered User" for example)

UPDATE user 
SET usergroupid = 'X' 
WHERE membergroupids = 'X,Y'
AND usergroupid = 'Z'

X = The id of the secondary group your users are currently in.
Y = The id of the second usergroup they are in.
Z = The primary usergroup those users are in currently. (Before the switchover)

After you have that set, you run this one to restore the secondary group and remove the converted one.

UPDATE user 
SET membergroupids = 'Y' 
WHERE membergroupids = 'X'
AND usergroupid = 'X'

X = The Old secondary you just converted to Primary.
Y = The secondary usergroup that the user is supposed to keep that way.

And vola! Just abit of back and forth between usergroup ID numbers and your set. Since normally you shouldnt need to touch anyone with a mod, super, or admin group number, no worries there. If however you get a user with 3 secondaries etc, you just take the one above, add another "," and the number of the group.

ALSO I do know this, you "should" in your secondary listings (If you had users with 2 or more secondaries) to list them in ORDER Meaning... 10,11. HOWEVER! I did notice oddly on my large community, that after I did it that way, still some users werent converted... so I had to reverse it to 11,10 which oddly that worked.

Next off.

"Paid Subscriptions" concerns. As you know, when your subscription ends, it will restore the setting or usergroup the user "was" in before the subscription started. So if you were like me, and all your users were in "Registered Users" with "Paid Subscriptions" as a secondary, you shouldnt have a issue.

However I did oddly notice this and it bothered me... if say a "Primary" usergroup who is a "Moderator" or role like that, and you enable a subscription or buy into one.. it seems to override that you were a "Moderator/Super" and turns you just into the plan "Paid Subscription" group. not sure what that is about.. but its just a caution.

Anyways, hope this lil guide helps someone out. If I knew how to make plugins... I would make a adminCP version of it where you just plug the numbers in and thats it... but oh well.. least this is something. Hope it helps people like me who DIDNT know how to mass move a secondary to primary without messing anything up.

Oh yes, do backup your table of course! All you need to backup if nothing else is the "user" table of your DB. Since thats all your working with. =)