Update user id by looking up two other tables

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   [e-mail]
2   [e-mail]
3   [e-mail]
4   [e-mail]
---------------------

---------------------
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   [e-mail]
9   [e-mail]
10  [e-mail]
17  [e-mail]
---------------------

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!

Answer

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