Need help moving users to different group
Need help moving users to different group
In order to more easily accommodate some custom work we need to migrate users from Group A into Group B. This goes way back to when we were on AC7 and had to put customers into a specific group at the checkout in order to apply volume discounts on certain products. In Gold customers are automatically placed into the _Default_ group, so there was no longer a need to place customers into a Retail group. Now we would like to have all customers that are in the Retail group, but not in _Default_, to be moved to _Default_. Can anyone help with a SQL Query that we could run to achieve this?
Rick Morris
Brewhaus (America) Inc.
Hot Sauce Depot
Brewhaus (America) Inc.
Hot Sauce Depot
Re: Need help moving users to different group
I think that I may have found a solution. Any feedback would be appreciated.
Remove duplicates (ie. remove users in more than one group so that each customer appears only once in the UserGroups table):
WITH CTE AS(
SELECT [UserID], [GroupID],
RN = ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY UserID)
FROM ac_UserGroups
)
DELETE FROM CTE WHERE RN > 1
Change group so that all users from the Retail group are placed into the _Default_ group instead:
UPDATE ac_UserGroups
SET GroupId='23'
WHERE GroupId='10';
Remove duplicates (ie. remove users in more than one group so that each customer appears only once in the UserGroups table):
WITH CTE AS(
SELECT [UserID], [GroupID],
RN = ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY UserID)
FROM ac_UserGroups
)
DELETE FROM CTE WHERE RN > 1
Change group so that all users from the Retail group are placed into the _Default_ group instead:
UPDATE ac_UserGroups
SET GroupId='23'
WHERE GroupId='10';
Rick Morris
Brewhaus (America) Inc.
Hot Sauce Depot
Brewhaus (America) Inc.
Hot Sauce Depot