Mysql Insert data in third table based on data from two tables [duplicate]

Question

I have the below tables:

order_temp table

|---------------------|------------------|------------------|
|      order_id       |    sap_number    |   product_name   |
|---------------------|------------------|------------------|
|          1          |         123      |     earphones    |
|---------------------|------------------|------------------|
|          2          |         123      |      battery     |
|---------------------|------------------|------------------|
|          3          |         456      |      charger     |
|---------------------|------------------|------------------|
|          4          |         789      |       phone      |
|---------------------|------------------|------------------|

order table

|---------------------|------------------|
|      order_id       |    sap_number    |
|---------------------|------------------|
|          1          |         123      |
|---------------------|------------------|
|          2          |         456      |
|---------------------|------------------|
|          3          |         789      |
|---------------------|------------------|

I want to insert data in order_item table which should have the same order_id if the sap_number is same. It should look like this:

order_item table:

|---------------------|------------------|------------------|
|     order_item_id   |     order_id     |   product_name   |
|---------------------|------------------|------------------|
|          1          |         1        |     earphones    |
|---------------------|------------------|------------------|
|          2          |         1        |      battery     |
|---------------------|------------------|------------------|
|          3          |         2        |      charger     |
|---------------------|------------------|------------------|
|          4          |         3        |       phone      |
|---------------------|------------------|------------------|

Please help me with an insert query for order_item table.

Yogi Prayoga · Accepted Answer · 2018-08-13 06:42:57Z

3

You can use with this simple query :

INSERT INTO order_item (order_id,product_name)
    SELECT b.order_id,a.product_name
    FROM order_temp a 
left join order b 
on a.sap_number=b.sap_number

answered Aug 13, 2018 at 6:42

Yogi Prayoga

693 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

munsifali · Accepted Answer · 2018-08-13 06:40:24Z

1

The easiest way you can do this is using sub queries:

INSERT INTO order_item(order_id, product_name)
VALUES ((SELECT order_id,product_name FROM order
         INNER JOIN order_temp ON order.sap_number = order_temp.sap_number);

answered Aug 13, 2018 at 6:40

munsifali

1,7432 gold badges24 silver badges44 bronze badges

Collectives™ on Stack Overflow

Mysql Insert data in third table based on data from two tables [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related