0

I have an old database, and a new database. The old database had various usergroups, with users inside of each usergroup. The new database has these same usergroups, but by accident, all users were put into only one usergroup.

I have the old list of usernames (or user ID's), and I want to move them into their corresponding usergroup.

How would I do this with an SQL query?

For example:

100 users in the new database are currently in user group "1", which I want to move to usergroup "2". Usergroup 1 already has 2,000 users, so I can't just move all users into Usergroup 2. But, I have the list of 100 user's user ID's (and usernames) in order to select them.

The relevant column names are: "userid" "usergroupid" "username"

and they are all in the table "user".

Thank you!

Note: The old database is not connected to the new database. I have the list of old user ID's/usernames in a comma delimited format.

user1446650
  • 1,197
  • 6
  • 15
  • 24
  • 1
    A few questions: 1) Do the users all have the same UserId values between both old and new databases? 2) Do the user groups have the same usergroupid values between old and new database? 3) When you refer to "Usergroup 2", does that mean the group with UserGroupId = 2? – STLDev Jul 26 '18 at 04:17
  • Yes to all of those questions. – user1446650 Jul 26 '18 at 04:36
  • @user1446650 I would have said the first thing to do was to load the CSV into a table in your active database. – MandyShaw Jul 26 '18 at 05:34

2 Answers2

1

Something like

update user set usergroupid=2 
where userid in (select userid from old_user where usergroupid=2)

Or select on names, whatever makes sense. Should give you the idea.

In answer to the comment if you have a list of ids in csv form, such as "0,5,9,25,35" etc rather than a table, then the query would be:

update user set usergroupid=2 
where userid in (0,5,9,25,35)
TomC
  • 2,759
  • 1
  • 7
  • 16
  • Sorry for the confusion. Where exactly would I input the list of userid's in the above query? FYI the old database is not connected to the new database, so all I have is the comma delimited list of old user ID's (which are the same on the new database). I've updated my question to reflect that point. Thank you! – user1446650 Jul 26 '18 at 04:39
  • Your post said you have the list of users - I assume its in a table? If its a hard coded list then you can enter it as "where userid in (id1,id2,id3...)" if the list is not too long. – TomC Jul 26 '18 at 05:40
  • This worked great and was a lot easier than having to import something. I had to remove the single quotes in the list, though (mysql error returned from mariadb if they were included). Thank you! – user1446650 Jul 26 '18 at 07:05
  • Don't forget to mark the answer as correct if you are happy with it! – TomC Jul 27 '18 at 02:13
0

Since your old users are listed in a CSV text file, you'll need to import that CSV into your new database. There are a few ways to do this, but one way would be to do this as suggested in the following Stack Overflow answer. Import these old users into a table named OldSystemUser. Follow the instructions here: Load CSV into MySQL

Move those users who are in user group 2 in the OldSystemUser table into usergroup 2 in the new database. Do this by using a subquery in the WHERE clause of your UPDATE statement:

UPDATE [user]
  SET [UserGroupId] = 2
  WHERE [UserId] IN (
    -- select all userIds that are in UserGroup 2 in the old database
    SELECT [UserId]
      FROM [OldSystemUser]
      WHERE [UserGroupId] = 2
  )

Once you've completed that, don't forget to remove the old table if it's no longer needed:

DROP TABLE [OldSystemUser]
STLDev
  • 5,950
  • 25
  • 36
  • Thanks for the response! I apologize for the confusion. The old database is not connected to the new server. All I have is a comma delimited csv of the old users. – user1446650 Jul 26 '18 at 04:32
  • Please update your question with this new data to make it more helpful to others trying to solve your problem. – STLDev Jul 26 '18 at 04:34