1

I have a "unusual" problem on MySQL Syntax.

I have 2 tables:

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

table2: trade_commodity and has 8 column id,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 for id_trade in table2.

Please take a look that table1 and table2 has different number of columns and id_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.

Community
  • 1
  • 1
agungandika
  • 166
  • 9

1 Answers1

3

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
Maximus2012
  • 1,799
  • 2
  • 12
  • 15
  • He said he wants to create a value for id_trade (which is not the PK, thus is also not AUTO INCREMENT). – Pedro Cordeiro Sep 09 '13 at 20:00
  • I have tried that code, but the result give null on table2.id_trade – agungandika Sep 09 '13 at 20:02
  • this code will take care of auto_increment for id_trade if it is declared as an auto increment field at the time of table creation. – Maximus2012 Sep 09 '13 at 20:02
  • 1
    @PedroCordeiro Non-PK columns can be `auto_increment` they just need to have an index – Matthew Sep 09 '13 at 20:02
  • @Matthew, I did not know that. That should do the trick then. – Pedro Cordeiro Sep 09 '13 at 20:03
  • 1
    @dika46 the id_trade column of table2 needs to be defined as auto increment along with PK. – Maximus2012 Sep 09 '13 at 20:03
  • table2.id_trade is not auto_increment. – agungandika Sep 09 '13 at 20:03
  • then what values do you want for table2.id_trade? Note that this is the only way to do it using MySQL only. If you want custom values then you need to write additional code using PHP/MySQL. If you could give an example of what do you want your table2 to look like then we can provide some suggestions for it. – Maximus2012 Sep 09 '13 at 20:05
  • @Maximus2012, table2.id_trade will be filled with value from PHP variable. – agungandika Sep 09 '13 at 20:06
  • do you have some sample values for that variable ? Is it a constant ? – Maximus2012 Sep 09 '13 at 20:08
  • @Maximus2012 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: 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' 10 comes from php var – agungandika Sep 09 '13 at 20:10
  • @Maximus2012 doesn't work. it says, "Unknown column '10' in field list" – agungandika Sep 09 '13 at 20:28