[FIXED] Update user id by looking up two other tables

Issue

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!

Solution

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

Answered By - Okechukwu Ossai