MySQL 使用另一个选择查询的结果将数据插入表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6354132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert data into table with result from another select query
提问by mmdel
I am seeking help on the following issue:
I have two tables
Table_1columns are itemid, locationid, quantity
我正在就以下问题寻求帮助：我有两个表的
Table_1列是itemid, locationid,quantity
Table_2columns are itemid, location1, location2, location3
Table_2列是itemid, location1, location2,location3
I want to copy data from Table_1(only quantitycolumn) into Table_2(into location1column). The itemidare same in both the tables(Table_1has 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
我想将数据从Table_1（仅quantity列）复制到Table_2（到location1列中）。该itemid均在表中相同（Table_1有重复项的ID），所以这就是我要复制到新表，并保持在一个单一的行中的所有数量与每个位置为一列的原因。我正在使用以下查询，但它不起作用
INSERT INTO
Table_2(location1)
(
SELECT qty
FROM Table_1
WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid
)
回答by Aziz Shaikh
If table_2is empty, then try the following insert statement:
如果table_2为空，则尝试以下插入语句：
insert into table_2 (itemid,location1)
select itemid,quantity from table_1 where locationid=1
If table_2already contains the itemidvalues, then try this update statement:
如果table_2已经包含这些itemid值，请尝试此更新语句：
update table_2 set location1=
(select quantity from table_1 where locationid=1 and table_1.itemid = table_2.itemid)
回答by Sanjit Majee
INSERT INTO `test`.`product` ( `p1`, `p2`, `p3`)
SELECT sum(p1), sum(p2), sum(p3)
FROM `test`.`product`;
回答by Mahendrasinh Motisinh Chauhan
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-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"));
