Question 63928 in PhpMyAdmin Question posted in
The official documentation can be found here.

Update user id by looking up two other tables – Phpmyadmin

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   [email protected]
2   [email protected]
3   [email protected]
4   [email protected]
---------------------

---------------------
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   [email protected]
9   [email protected]
10  [email protected]
17  [email protected]
---------------------

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!

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search