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
)
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)
INSERT INTO `test`.`product` ( `p1`, `p2`, `p3`)
SELECT sum(p1), sum(p2), sum(p3)
FROM `test`.`product`;
Below is an example of such a query:
INSERT INTO [93275].[93276].[93277].[93278] ( [Mobile Number], [Mobile Series], [Full Name], [Full Address], [Active Date], company ) IN 'I:\For Test\90-Mobile Series.accdb
SELECT [1].[Mobile Number], [1].[Mobile Series], [1].[Full Name], [1].[Full Address], [1].[Active Date], [1].[Company Name]
FROM 1
WHERE ((([1].[Mobile Series])="93275" Or ([1].[Mobile Series])="93276")) OR ((([1].[Mobile Series])="93277"));OR ((([1].[Mobile Series])="93278"));
Source: Stackoverflow.com