mysql inserting into database based on result of other query

mysql inserting into database based on result of other query

http://stackoverflow.com/questions/6354132/insert-data-into-table-with-result-from-another-select-query

If table_2 is empty then try the following insert statement:

insert into table_2 (itemid,location1) select itemid,quantity from table_1 where locationid=1

If table_2 already contains itemid values then try this update statement:

update table_2 set location1=
(select quantity from table_1 where locationid=1 and table_1.itemid = table_2.itemid)

share|improve this answer

Question:

I am seeking help on the following issue: I have two tables Table_1 columns are itemid,locationid, quantity

Table_2 columns are itemid, location1, location2, location3

I want to copy data from Table_1 (only quantity column) into Table_2 (into location1 column). The itemid are same in both the tables(Table_1 has duplicate item id's) so that's the reason I want to copy to a new table and keep all quantity in one single row with each location as a column.i am using the below query but does'nt work

INSERT INTO Table_2(location1) (SELECT qty FROM Table_1 WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid)

mysql select insert

share|improve this question