php - How can I copy rows from one to another table with a different number of columns

570

I have a "unusual" problem on MySQL Syntax.

I have 2 tables:

table1: commodityAttributes and has 7 columnid,id_commodity,parameter,value,units,type,notes

table2: trade_commodity and has 8 columnid,id_trade,id_commodity,parameter,value,units,type,notes

note: primary key for both tables are 'id', which is I didn't want to copy

What I want, is copy all column from table1 to table2 but also create value forid_trade in table2.

Please take a look that table1 and table2 has different number of columns andid_trade on table2 IS NOT auto_increment.

Here is the example of the actual result and desired result:

table1: 
id,id_commodity,parameter,value,units,type,notes 
1, 1, 'Ash','10','%','min','ash for a commodity' 
2, 1, 'Ash 2','15','%','max','ash for a commodity' 

after do copy procedure, it produce:

table2:
id,id_trade,id_commodity,parameter,value,units,type,notes 
1,NULL,1, 'Ash','10','%','min','ash for a commodity' 
2,NULL,1, 'Ash 2','15','%','max','ash for a commodity' 

what I want is the result of table2:

id,id_trade,id_commodity,parameter,value,units,type,notes
1,10,1, 'Ash','10','%','min','ash for a commodity'
2,10,1, 'Ash 2','15','%','max','ash for a commodity'

which is'10' for id_trade comes from php var.

How can I accomplish this? Or is there another tricky? Btw, I am using PHP and MySQL to work on this task.

EDIT: I see the "similiar" problem with this, but I found that he is trying to use command rather than value MYSQL: How to copy an entire row from one table to another in mysql with the second table having one extra column?

Please kind help me, thank you.

796

Answer

Solution:

Does this work:

insert into table2 (id_commodity,parameter,value,units,type,notes)
select id_commodity,parameter,value,units,type,notes
from table1

UPDATE: In light of additional information provided by the OP, this should be the solution that the OP is looking for:

insert into table2 (id_trade, id_commodity,parameter,value,units,type,notes)
select '10', id_commodity,parameter,value,units,type,notes
from table1

People are also looking for solutions to the problem: mysql - How to create nested category list from category-strings in PHP

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.