带有多个嵌套 SELECT 的 MySQL INSERT

发布于 12-06 20:38 字数 259 浏览 0 评论 0原文

这样的查询可以吗? 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')

Is a query like this possible? MySQL gives me an Syntax error. Multiple insert-values with nested selects...

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')

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

从﹋此江山别2024-12-13 20:38:07

我刚刚测试了以下内容(有效):

insert into test (id1, id2) values (1, (select max(id) from test2)), (2, (select max(id) from test2) );

我想问题是你的选择周围没有 () ,因为没有它这个查询就无法工作。

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));

I imagine the problem is that you haven't got ()s around your selects as this query would not work without it.

梦里寻她2024-12-13 20:38:07

当您有这样的子查询时,它必须仅返回一列和一行。如果您的子查询只返回一行,那么您需要在它们周围加上括号,正如 @Thor84no 注意到的那样。

如果它们返回(或可能返回)多于行,请尝试以下操作:

INSERT INTO pv_indices_fields (index_id, veld_id)   
   SELECT '1', id 
   FROM pv_fields 
   WHERE col1='76' 
   AND col2 IN ('val1', 'val2')

或者如果您的条件非常不同:

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' )

When you have a subquery like that, it has to return one column and one row only. If your subqueries do return one row only, then you need parenthesis around them, as @Thor84no noticed.

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' )
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文