SQL 如何使用具有多个结果的子查询将值插入表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9692319/
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 can I insert values into a table, using a subquery with more than one result?
提问by Futuretec
I really would appreciate your help.
我真的很感激你的帮助。
Probably it's a quite simple problem to solve - but I'm not the one .. ;-)
可能这是一个很容易解决的问题 - 但我不是那个.. ;-)
I have two tables in SQL Server:
我在 SQL Server 中有两个表：
- article
- prices
- 文章
- 价格
Now I want to select a certain set of ids and insert some entries into the prices-table with those ID.
现在我想选择一组特定的 ID，并使用这些 ID 将一些条目插入到价格表中。
e.g. (wrong and not working SQL)
例如（错误且不工作的 SQL）
INSERT INTO prices (group, id, price)
VALUES (7, (select articleId from article WHERE name LIKE 'ABC%'), 1.50);
SQL Error -> subquery has more than 1 value
SQL 错误 -> 子查询有 1 个以上的值
thanks for help
感谢帮助
回答by Mike Ryan
You want:
你要：
insert into prices (group, id, price)
select
7, articleId, 1.50
from article where name like 'ABC%';
where you just hardcode the constant fields.
您只需对常量字段进行硬编码。
回答by Stefan H
Try this:
尝试这个：
INSERT INTO prices (
group,
id,
price
)
SELECT
7,
articleId,
1.50
FROM
article
WHERE
name LIKE 'ABC%';
回答by Taryn
If you are inserting one record into your table, you can do
如果您在表中插入一条记录，则可以执行
INSERT INTO yourTable
VALUES(value1, value2)
But since you want to insert more than one record, you can use a SELECT FROMin your SQL statement.
但是由于要插入多条记录，可以SELECT FROM在 SQL 语句中使用 a 。
so you will want to do this:
所以你会想要这样做：
INSERT INTO prices (group, id, price)
SELECT 7, articleId, 1.50
from article
WHERE name LIKE 'ABC%'
回答by Terkel
INSERT INTO prices (group, id, price)
SELECT 7, articleId, 1.50 FROM article WHERE name LIKE 'ABC%'
回答by Muhannad A.Alhariri
the sub query looks like
子查询看起来像
insert into table_name (col1,col2,....) values (select col1,col2,... FROM table_2 ...)
hope this help
希望这有帮助
回答by Teja
INSERT INTO prices(group, id, price)
SELECT 7, articleId, 1.50
FROM article where name like 'ABC%';
