SQL - 在 INSERT VALUES 中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6254913/
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
SQL - Select inside INSERT VALUES
提问by aocferreira
How can I make a SELECT inside an INSERT operation?
如何在 INSERT 操作中进行 SELECT？
insert into tableX (a_id, b_id)
VALUES ((SELECT service_id
FROM tableY
WHERE id = 10, 2));
but get an error.. What's wrong there?
但得到一个错误..有什么问题吗？
Thanks
谢谢
回答by Dave Costa
While my original answer gave a working solution, I was actually wrong about the cause of the error. There is nothing wrong with using a scalar subquery inside a VALUES clause. The problem with the statement in the question is simply that one parenthesis is in the wrong place; the scalar subquery must be enclosed in parentheses.
虽然我最初的答案给出了一个有效的解决方案，但我实际上对错误的原因是错误的。在 VALUES 子句中使用标量子查询没有任何问题。问题中陈述的问题只是一个括号放错了地方；标量子查询必须括在括号中。
This should work:
这应该有效：
insert into tableX (a_id, b_id)
VALUES (
(SELECT service_id
FROM tableY
WHERE id = 10)
, 2
);
Original Answer
原答案
VALUEScan only be used in conjunction with literal values. However, literal values can be used in a subquery. Do this:
VALUES只能与文字值结合使用。但是，可以在子查询中使用文字值。做这个：
insert into tableX (a_id, b_id) SELECT service_id, 2 FROM tableY WHERE id = 10
回答by Chandu
You don't need the values key word and also you can add the default value 2 for b_id column in the select list instead of adding it after the SELECT statement
您不需要 values 关键字，也可以在选择列表中为 b_id 列添加默认值 2 而不是在 SELECT 语句之后添加它
Try this:
尝试这个：
INSERT INTO tableX (a_id, b_id)
SELECT service_id, 2
FROM tableY
WHERE id = 10
回答by hvgotcodes
you don't need the valuesword.
你不需要这个values词。
here is some documentation for mysql
这是 mysql 的一些文档
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
also, when you specify the columns into which the values should be inserted, you need to make sure your select returns the same number/types appropriate to what you specified.
此外，当您指定应插入值的列时，您需要确保您的选择返回与您指定的相同的数字/类型。
回答by Christopher Armstrong
You're telling the DBMS that you're inserting two values (a_id, b_id) but only selecting one (service_id).
您告诉 DBMS 您要插入两个值 (a_id, b_id) 但只选择一个 (service_id)。
回答by maple_shaft
Try:
尝试：
insert into tableX (a_id, b_id)
SELECT service_id, 4 as QUESTIONMARK FROM tableY WHERE id in (10, 2);
This works on many database engines however we don't know what environment you are working on.
这适用于许多数据库引擎，但是我们不知道您在什么环境下工作。
EDIT: What is the second insert supposed to be?
编辑：第二个插入应该是什么？
回答by Aditya
In MySQLone can insert dynamic values. Here is the sample:
在MySQL一个可以插入动态值。这是示例：
INSERT INTO Item_Info (`Back_Ground_Color`,)
VALUES ( (select concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6))));
