mysql - SELECT AND INSERT QUERY into multiple table with conditions

Question

I dont know it is possible or not in MySQl, but I have to import the old database table to new normalized database table. I have 2 table user and user_roles in new one and the previous one only has admin

newdb.users Table
user_id | username | password

newdb.user_roles Table
user_id | role_id

old database table

olddb.admin Table
id | username | password | user_type
1  | user1    | ***      | admin
3  | user7    | ***      | staff

admin role_id is 1 and staff role_id is 2

So the new table after SQL insert should look like.

newdb.users Table
user_id | username | password
1       | user1    | ***   
3       | user7    | ***   

newdb.user_roles Table
user_id | role_id
1       |  1
3       |  2

How to do this using MYSQL query. Thanks.

Answer 1

You should be basing this information on a roles table:

create table roles (
    int id auto_increment primary key,
    role_name varchar(255)
);

insert into roles (role_name)
    select distinct user_type
    from old.admin;

Then:

insert into new.users(username, password)
    select distinct username, password
    from old.admin;

(distinct is not necessary if username is not repeated in original table.)

And:

insert into user_roles(user_id, role_id)
    select u.id, r.id
    from new.users u join
         old.admin a
         on u.user_name = a.user_name and
            u.password = a.password join  -- this condition may not be necessary
         new.roles r
         on a.user_type = r.role_name;

Although you can hard-code values into the query, I think it is a good idea to let the database do the work for you. This approach is more general.

Answer 2

In order to insert roles from old table to new table:

INSERT INTO newdb.user_roles 

SELECT 
id,
CASE WHEN user_type='admin' THEN 1
     WHEN user_type ='staff' THEN 2 END AS role_id
FROM olddb.admin 

In order to insert users from old table to new table:

INSERT INTO newdb.users
SELECT 
id,
username,
password
FROM olddb.admin