英文:
how to insert data with subquery that returns more than on value?
问题
我有两个表格，company_list 和 company_details。company_list 表格有一个 company_id 列，而 company_details 表格包含与 company_id 相关的一些公司信息。我想要将数据插入到 company_details 表格中，但是只包括在 company_details 表格中没有的 company_id。
我尝试使用以下查询，但是出现了错误。
INSERT INTO company_details (company_id, detail_found)
VALUES ((SELECT company_id FROM company_list WHERE company_id NOT IN (SELECT company_id FROM company_details)), '未找到数据');
错误信息为：
Msg 512, Level 16, State 1, Line 27
子查询返回多个值。当子查询跟随 =、!=、<、<=、>、>= 等运算符时，或者当子查询用作表达式时，不允许出现这种情况。
语句已终止。
如何使用返回多个值的子查询插入数据到表格中？
英文:
I have two tables company_list and company_details. The company_list table has a company_id column, and the company_details has some information related to the company with the company_id. I wanted to insert data into the company_details table with the company_id not in the company_details table.
I tried it with the following query but it gave me an error.
INSERT INTO company_details (company_id,detail_found)
VALUES ((SELECT company_id FROM company_list WHERE company_id NOT IN (SELECT company_id FROM company_details)), 'No data found');
The error is:
> Msg 512, Level 16, State 1, Line 27
>
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
>
> The statement has been terminated.
How to insert data into the table with a subquery that returns more than one value?
答案1
得分: 2
你可以使用一个插入选择语句（不包括 values 子句），并将字符串字面值查询为选择语句的一列：
INSERT INTO company_details (company_id, detail_found)
SELECT company_id, '未找到数据'
FROM company_list
WHERE company_id NOT IN (SELECT company_id FROM company_details)
英文:
You can use an insert-select statement (without the values clause), and query the string literal as one of the select's columns:
INSERT INTO company_details (company_id,detail_found)
SELECT company_id, 'No data found'
FROM company_list
WHERE company_id NOT IN (SELECT company_id FROM company_details)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论