子查询返回超过1行 [英] Subqueries return more than 1 row

问题描述

当我尝试执行以下查询时，出现错误消息子查询返回多于1行".我应该如何重写查询?

Hi,
when i try to execute the below query am getting an error messahe "subquery returns more than 1 row " . How should i rewrite the query ?

Update table1 set facilityID =
(select facilityID FROM table2 as a inner join table3 as b
on a.name =b.PROV_ORG_NAME)

谢谢.

推荐答案

使用SELECT TOP 1.我认为这可能会有所帮助.

Use SELECT TOP 1. I think it may be helpful.

Update table1 set facilityID =
(select TOP 1 facilityID FROM table2 as a inner join table3 as b
on a.name =b.PROV_ORG_NAME)

VJ Reddy的解决方案将使您摆脱错误信息.但是问题就不同了.
通过UPDATE查询，您将使用一个值更新table1的所有行. IE. table1的所有行都具有table2中的那个facilityID值，该值是从子查询中检索的第一个值.我很确定那不是故意的，是吗?
这意味着您在正确的UPDATE查询中缺少WHERE子句.您能用简单的英语描述要使table1中正确"行的正确"工具ID所必须应用的进一步条件吗?

VJ Reddy''s solution will get you rid of the error message. But the problem is different.
With your UPDATE query, you are about to update ALL rows of table1, with one value. I.e. all rows of table1 will have that facilityID value from table2 which was retrieved as the first value from the subquery. I am pretty sure that that is not intended, is it?
This means that you are missing a WHERE clause in the UPDATE query proper. Could you describe in plain English which further conditions must be applied to get the "correct" facilityID to the "correct" row in table1?

您还可以尝试以下代码:

You can also try this code:

Update table1 set facilityID =
(select a.facilityID FROM table2 a, table3 b
WHERE a.name =b.PROV_ORG_NAME LIMIT 0,1)

好吧，您的整个查询也需要重新考虑.我猜你可能需要做一个函数来解决这个问题.

Well, also your entire query needed to be rethink-ed. i can guess you might needed to make a function to solve the issue.

这篇关于子查询返回超过1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！