如何在 MySQL 的“插入”语句中使用“选择”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11003442/
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
How to use 'select ' in MySQL 'insert' statement
提问by user1074122
I'm trying to insert additional rows into a table which requires a value to be retrieved from another table. Below is an example query:
我正在尝试将其他行插入到需要从另一个表中检索值的表中。下面是一个示例查询：
insert into a.grades (rollno, grade)
values(select rollno from b.students where ssn=12345, 'A');
Structure of b.studentstable is rollno, ssn, name.
b.students表的结构是rollno, ssn, name.
I knew the above query is wrong. Is there a way to retrieve 1 value from other table while inserting a row?
我知道上面的查询是错误的。有没有办法在插入行时从其他表中检索 1 个值？
回答by Jonathan Leffler
INSERT INTO a.grades (rollno, grade)
SELECT rollno, 'A' FROM b.students WHERE ssn = 12345;
Some DBMS would accept the following, with an extra set of parenthesis around the SELECT statement:
一些 DBMS 会接受以下内容，并在 SELECT 语句周围加上一组额外的括号：
INSERT INTO a.grades (rollno, grade)
VALUES((SELECT rollno FROM b.students WHERE ssn = 12345), 'A');
回答by yAnTar
Columns in insert into and select must be equal
insert into 和 select 中的列必须相等
INSERT INTO grades (field1, field2)
SELECT field1, field2 from students where ssn=12345;
回答by SagitSri
Tables from two different databases!
来自两个不同数据库的表！
Database1 - person
数据库 1 - 人
Database2 - order
Database2 - 订单
- Table - per_details
- Table - or_details
- 表 - per_details
- 表 - or_details
Here the insert query used under Database 2!
这里是在数据库 2 下使用的插入查询！
INSERT INTO `or_details`(`per_name`) VALUES ( (SELECT person.per_details.per_name from person.per_details WHERE person.per_details.id=1001) );
