SQL 带有多个嵌套 SELECT 的 MySQL INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7567802/
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 INSERT with multiple nested SELECTs
提问by netiul
Is a query like this possible? MySQL gives me an Syntax error. Multiple insert-values with nested selects...
这样的查询可能吗？MySQL 给了我一个语法错误。具有嵌套选择的多个插入值...
INSERT INTO pv_indices_fields (index_id, veld_id)
VALUES
('1', SELECT id FROM pv_fields WHERE col1='76' AND col2='val1'),
('1', SELECT id FROM pv_fields WHERE col1='76' AND col2='val2')
回答by Thor84no
I've just tested the following (which works):
我刚刚测试了以下内容（有效）：
insert into test (id1, id2) values (1, (select max(id) from test2)), (2, (select max(id) from test2));
insert into test (id1, id2) values (1, (select max(id) from test2)), (2, (select max(id) from test2));
I imagine the problem is that you haven't got ()s around your selects as this query would not work without it.
我想问题是你的选择周围没有 ()s，因为没有它，这个查询将无法工作。
回答by ypercube??
When you have a subquery like that, it has to return one column and one rowonly. If your subqueries do return one row only, then you need parenthesis around them, as @Thor84no noticed.
当您有这样的子查询时，它必须只返回一列和一行。如果您的子查询只返回一行，那么您需要在它们周围加上括号，正如@Thor84no 所注意到的。
If they return (or could return) more than row, try this instead:
如果他们返回（或可能返回）多于行，请尝试以下操作：
INSERT INTO pv_indices_fields (index_id, veld_id)
SELECT '1', id
FROM pv_fields
WHERE col1='76'
AND col2 IN ('val1', 'val2')
or if your conditions are very different:
或者如果您的条件非常不同：
INSERT INTO pv_indices_fields (index_id, veld_id)
( SELECT '1', id FROM pv_fields WHERE col1='76' AND col2='val1' )
UNION ALL
( SELECT '1', id FROM pv_fields WHERE col1='76' AND col2='val2' )
