MySQL Insert into table with different column name

Question

I have 3 tables. "machines", "products", "machine_products".

The idea in my mind is to store machine information in "machines" table and set "machine_id" column as PRIMARY & UNIQUE and do same with "products" table the column name here is "product_id".

One machine may have one or more product so they both match in "machine_products" table

Table samples are below.

machines table

+------------+------------------+
| machine_id |   machine_code   |
+------------+------------------+
|      1     | C01.C03.C23 M.1  |
+------------+------------------+
|      2     | C07.08.09.10 M.1 |
+------------+------------------+
|      3     | C11.12 MONT.1    |
+------------+------------------+
|      4     | C13.14.21 MONT.1 |
+------------+------------------+
|      5     | C22 MONT.1       |
+------------+------------------+

products table

+------------+--------------+
| product_id | product_code |
+------------+--------------+
|      1     | C01.00       |
+------------+--------------+
|      2     | C01.11       |
+------------+--------------+
|      3     | C01.21       |
+------------+--------------+
|      4     | C03.00       |
+------------+--------------+
|      5     | C03.01       |
+------------+--------------+

machine_products table

+----+------------+------------+
| id | machine_id | product_id |
+----+------------+------------+
|  1 |      1     |     70     |
+----+------------+------------+
|  2 |      1     |     73     |
+----+------------+------------+
|  3 |      1     |     78     |
+----+------------+------------+
|  4 |      1     |     83     |
+----+------------+------------+
|  5 |      2     |     100    |
+----+------------+------------+
|  6 |      2     |     208    |
+----+------------+------------+
|  7 |      3     |     101    |
+----+------------+------------+
|  8 |      3     |     108    |
+----+------------+------------+
|  9 |      3     |     112    |
+----+------------+------------+
| 10 |      4     |     113    |
+----+------------+------------+

My problem is I want to insert data to "machine_products" table by using "machine_code" column data

How do I add records to the machine_products table when what I have are the product_code and machine_code?

You presented the context well, but now you need to do a few things: 1. Rephrase the question better: How do I add records to the machine_products table when what I have are the product_code and machine_code? — Emmanuel Okeke
– Emmanuel Okeke, Commented Jun 10, 2019 at 14:32
Also, it would help for you to enclose the table names in code tags using the backtick - machines, products, machine_products — Emmanuel Okeke
– Emmanuel Okeke, Commented Jun 10, 2019 at 14:33
Thanks for advice. This is my first post so I couldn't entirely figure out how things work. — lll
– lll, Commented Jun 10, 2019 at 15:34

Jayish · Accepted Answer · 2019-06-10 17:58:55Z

1

Can you try this

Insert into machine_products ( machine_id, product_id) values( (select machine_id from machines where machine_code = $machine_code), (select product_id from products where product_code=$product_code))

You can the substitute $machine_code and $product_code for your own values

answered Jun 10, 2019 at 17:58

Jayish

861 silver badge5 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2019-06-10 13:19:09Z

0

You could use a cartesian product between machine and product
and insert select

insert into  machine_products ( machine_id, product_id)
select  a.id, b.id 
from machine a 
cross join products b

this way all the possibile combination betwen machine and product are inserted in machine_products table

answered Jun 10, 2019 at 13:19

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

lll Over a year ago

How will I enter values in this statement?

Arulraj · Accepted Answer · 2019-06-11 06:28:27Z

0

1) Insert the data in Machines table Machines table image You get the machine table insert id ($machine_id)

2) Insert in the Products table product table image and get the product_id ($product_id) Note : You should update the $machine id in product table

3) Insert the machine_products table machine_products table image Note : Here you should update the $machine id & $product id

edited Jun 11, 2019 at 6:28

answered Jun 10, 2019 at 13:26

Arulraj

115 bronze badges

1 Comment

lll Over a year ago

It doesn't make sense for me. I will probably need a loop to check through machine_id by giving machine_code to it.

Collectives™ on Stack Overflow

MySQL Insert into table with different column name

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related