How can I move all old user groups to the new _Default_

mlarsen · Post by **mlarsen** » Thu Dec 18, 2014 4:41 am

I have a lot of users who are part of a one person group with the group name of their email address. Is there a bulk way of converting all of them to the new _Default_ Group?

I tried the following

update ac_UserGroups
set groupid = 383 ' _Default_ group
where groupid <> 383
and groupid > 9 ' all my admin groups are below nine.

I get this error
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ac_UserGroups' with unique index 'ac_UserGroups_CK'.
The statement has been terminated.

I don't see a way to do this in the admin either other than one at a time and that is WAY to many...

Thanks

jmestep · Post by **jmestep** » Fri Dec 19, 2014 3:19 am

It looks like you might need to add a criteria that the userid is not already in group 383. It looks like Able adds users to the default group at upgrade time.

Post by **Naveed** » Fri Dec 19, 2014 5:18 am

Use this query, it will add new user -> group relation for all users except admin users (groupId > 9) and anonymous users and users which are already not assigned to default group.

Code: Select all

INSERT INTO ac_UserGroups(UserId, GroupId)
	SELECT UserId, 383  
	FROM ac_Users WHERE UserId NOT IN 
		(SELECT DISTINCT U.UserId FROM ac_Users U LEFT JOIN ac_UserGroups UG ON U.UserId = UG.UserId 
		WHERE (UG.GroupId = 383 OR UG.UserId IS NOT NULL OR UG.GroupId < 9)) 
	AND IsAnonymous = 0

mlarsen · Post by **mlarsen** » Fri Dec 19, 2014 6:35 am

Thanks for the script but it returns 0 rows affected and in fact did not affect any of the user group assignments. Any other ideas?

rmaweb · Post by **rmaweb** » Fri Dec 19, 2014 2:13 pm

Are you going to use the old groups anymore? If not just delete them and if there are users in that group, the admin page will go to another that will allow you to select the group you want those users to be moved to.

mlarsen · Post by **mlarsen** » Fri Dec 19, 2014 4:22 pm

Yes I can do that but each user is in their own group. so I have LOTS of groups each with 1 member. I don't know if that was the original design of Able or a result of my accounting system integration with Able but that is where it is and I don't have the time to change the group of each user one at a time. I was hoping for a script to move them all to the _Default_ Group and then I can just delete the unused groups.

rmaweb · Post by **rmaweb** » Fri Dec 19, 2014 7:28 pm

Ah. Well if you don't have a time emergency , I might be able to have a solution available after Christmas that you can use.

jmestep · Post by **jmestep** » Mon Dec 22, 2014 4:12 am

Did you look at some of the users in the admin to make sure they are not in the default group already? Or do you have some custom code to create users that overrode the upgrade?

mlarsen · Post by **mlarsen** » Tue Dec 23, 2014 3:03 pm

I resorted to brute force and got everyone in the office removing the people for the groups and assigning them to the default. tedious but done.

AbleCommerce Shopping Cart Software

How can I move all old user groups to the new _Default_

How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_

Re: How can I move all old user groups to the new _Default_