MySQL 如何使用返回多行的 SELECT 子查询插入表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9422529/
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
MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
提问by stackoverflow
MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
MySQL 如何使用返回多行的 SELECT 子查询插入表？
INSERT INTO Results
(
People,
names,
)
VALUES
(
(
SELECT d.id
FROM Names f
JOIN People d ON d.id = f.id
),
(
"Henry"
),
);
I WANTto populate the new table with all results returning from this subquery. How do I do this without getting a ERROR 1242 (21000): Subquery returns more than 1 row
我WANT来填充新表与此子查询返回的所有结果。如何在不出现错误 1242 (21000) 的情况下执行此操作：子查询返回多于 1 行
回答by Ryan
INSERT INTO Results (People, names )
SELECT d.id, 'Henry'
FROM Names f
JOIN People d ON d.id = f.id
Combine the static string Henrywith your SELECTquery.
将静态字符串Henry与您的SELECT查询结合起来。
回答by Umar Enesi Ibrahim
INSERT INTO Results
(
People,
names,
)
VALUES
(
(
SELECT d.id
FROM Names f
JOIN People d ON (d.id = f.id) limit 1
),
(
"Henry"
),
);
回答by MiggityMac
Here is what I've found that works well. It is a little long but many times extra data needs to be shuffled around.
这是我发现效果很好的方法。它有点长，但很多时候需要重新整理额外的数据。
Insert multiple rows into table1 from table2 with values. EXAMPLES:
将带有值的表 2 中的多行插入到表 1 中。例子：
INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT col1,col2,col3,col4,col5
FROM table2 t2
WHERE t2.val2 IN (MULTIPLE VALUES)
AND (Another Conditional);
You can insert hard coded values to get insert multiple rows with repeat data:
您可以插入硬编码值以插入具有重复数据的多行：
INSERT INTO table1 (col1, col2, col3, col4, col5)
SELECT "Value", col2, col3, "1900-01-01","9999-12-31"
FROM table2 t2
WHERE t2.val2 IN (MULTIPLE VALUES)
AND (Another Conditional);
Note that: "Value","1900-01-01","9999-12-31" will repeat across all rows inserted.
请注意：“Value”、“1900-01-01”、“9999-12-31”将在插入的所有行中重复。
回答by triclosan
INSERT INTO Results
(
People,
names,
)
SELECT d.id, 'Henry'
FROM Names f
JOIN People d ON d.id = f.id
回答by Sveteek
The reason of this error (Subquery returns more than 1 row) is that you use parenthesis (). Look more careful to the best answer. It doesn't contain parethesis around subquery
此错误（子查询返回超过 1 行）的原因是您使用了括号()。仔细看最佳答案。它不包含子查询周围的括号
回答by Fabio Marano
In MySql multiple values from strings can be inserted like the following avoiding duplicates. Thanks.
在 MySql 中，可以像下面这样插入字符串中的多个值，避免重复。谢谢。
insert into brand(name) select * from (
select 'Fender' as name
union select 'a'
union ..... ) t
where not exists (select 1 from brand t2 where t2.name COLLATE latin1_general_ci = t.name COLLATE utf8mb4_unicode_ci )
回答by sahmad
insert into ec_element(parentid,name) select elementid , 'STARTUP' from ec_element where name = 'BG';
插入 ec_element(parentid,name) select elementid , 'STARTUP' from ec_element where name = 'BG';
insert statement takes values elementid from the table found with condition fulfilled and a label string.
insert 语句从满足条件的表中获取值 elementid 和标签字符串。
