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
)