Menu
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 it doesn't work
INSERT INTO Table_2(location1) ( SELECT qty FROM Table_1 WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid ) 
281
Use the results of a query as a field in another query. You can use a subquery as a field alias. Use a subquery as a field alias when you want to use the subquery results as a field in your main query. Note: A subquery that you use as a field alias cannot return more than one field.
In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to get records from the source table and insert it into the destination table. We can combine both columns and defined values in the SQL INSERT INTO SELECT statement.
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 the 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
