Update user id by looking up two other tables-CodePudding

I have a table/sql issue I don't know how to solve.

I need to update/create a table of user ids with order ids.

Therefore I have to get a new user_id, by searching for the email in an old list. With the email adress I need to look up the new user id.

So the logic is like: order_id -> look at the old user_id -> look at the email -> look at the new user_id

I tried to create an example:

---------------------
TABLE: USERS_OLD (a list of user ids and an email adress)
id  email
1   test1@email.com
2   test2@email.com
3   test3@email.com
4   test4@email.com
---------------------

---------------------
TABLE: USER_ORDERS_OLD (the connection of an order id with a user id)
user_id     order_id
1           DLEFGM
2           OPDFGT
3           UZDFGP
4           POIDSX
---------------------

---------------------
TABLE: USERS_NEW (a new list of users id with the same emails from table USERS_OLD)
id  email
5   test1@email.com
9   test2@email.com
10  test3@email.com
17  test4@email.com
---------------------

What I want to create:
---------------------
TABLE: USER_ORDERS_NEW
user_id     order_id
5           DLEFGM
9           OPDFGT
10          UZDFGP
17          POIDSX
---------------------

I have no idea how to do that action. I don't even know what to search for.

What I managed to do is a LEFT JOIN sql statement to compare the user ids and create a list of matching user_ids. But I have no idea how to look up over even more tables...

Hopefully someone can help me. If it's easier I could also try to do it in spreadsheets.

Thanks in advance!

CodePudding user response：

With your example you should be able to accomplish this with this query

INSERT INTO user_orders_new (user_id, order_id) SELECT usr_new.id, ord_old.order_id 
FROM users_old usr_old JOIN users_new usr_new ON usr_new.email = usr_old.email
                       JOIN user_orders_old ord_old ON ord_old.user_id = usr_old.id;

You may want to opt for a LEFT JOIN on the old order table if you intend to migrate the ids regardless of whether they have had an order or not although I assume that's not the intention to populate the new order table with new users ids that haven't made an order.

CodePudding user response：

Assuming you just want to return the query then use;

SELECT u3.id, u2.order_id
FROM USERS_OLD u1
JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
JOIN USERS_NEW u3 ON u1.email = u3.email;

However, if you want to write the result into a new table, then you need to create the table first.

CREATE TABLE USER_ORDERS_NEW (user_id INTEGER, order_id VARCHAR(50));

INSERT INTO USER_ORDERS_NEW (user_id, order_id)
       SELECT u3.id, u2.order_id
       FROM USERS_OLD u1
       JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
       JOIN USERS_NEW u3 ON u1.email = u3.email;

See Demo