oracle 如何解决 ORA-01427 错误（单行子查询返回多于一行）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29420261/
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 resolve an ORA-01427 error (Single-row subquery returns more than one row)?
提问by KinsDotNet
My query keeps returning an ORA-01427 error and I don't know how to resolve it.
我的查询不断返回 ORA-01427 错误，我不知道如何解决。
update db1.CENSUS set (notes)
=
(
select notes
from db2.CENSUS cen
where db1.CENSUS.uid = cen.uid
)
where headcount_ind = 'Y' and capture_FY = '2015'
回答by David Faber
You are getting the error because there exists more than one row in db2.CENSUSfor at least value of uid. (There could be more.) You can figure out which values of uidare causing the issue by doing the following:
因为存在着多行你所得到的误差db2.CENSUS为至少价值uid。（可能还有更多。）您可以uid通过执行以下操作找出导致问题的值：
SELECT uid, COUNT(*)
FROM db2.census
GROUP BY uid
HAVING COUNT(*) > 1;
At that point you can do a number of things. You can delete the extra rows (maybe there aren't that many and you don't want them anyway) and update as in your original query, or you can use aggregation in the subquery you're using to update, e.g.:
那时你可以做很多事情。您可以删除额外的行（也许没有那么多，而且您也不想要它们）并按照原始查询进行更新，或者您可以在用于更新的子查询中使用聚合，例如：
update db1.CENSUS set (notes)
=
(
select MAX(notes)
from db2.CENSUS cen
where db1.CENSUS.uid = cen.uid
)
where headcount_ind = 'Y' and capture_FY = '2015';
In addition, with your query the way it is above, if there is not a corresponding value of notesin db2.CENSUSfor some value of db1.CENSUS.uid, db1.CENSUS.noteswill be set to NULL. Maybe that's the behavior you want? If not, you'll want something like the following:
另外，按照上面的方式查询，如果 的某个值没有对应的notesindb2.CENSUS值db1.CENSUS.uid，db1.CENSUS.notes则将设置为NULL。也许这就是你想要的行为？如果没有，您将需要以下内容：
UPDATE db1.census c1
SET c1.notes = ( SELECT max(c2.notes)
FROM db2.census c2
WHERE c2.uid = c1.uid )
WHERE c1.headcount_ind = 'Y'
AND c1.capture_FY = '2015'
AND EXISTS ( SELECT 1 FROM db2.census c2
WHERE c2.uid = c1.uid );
回答by Barak Kedem
You can also use analytics functions in your sub query such as
您还可以在子查询中使用分析功能，例如
RANK() OVER (PARTITION BY census.uid ORDER BY 1 DESC)
RANK() OVER（PARTITION BY census.uid ORDER BY 1 DESC）
and then choose the first rank or second rank
然后选择第一排或第二排
回答by Learner
Your update query expects the inner query to resolve to a single record, but it is actually an array. If each result in the inner query has the same value, you can do this:
您的更新查询期望内部查询解析为单个记录，但它实际上是一个数组。如果内部查询中的每个结果都具有相同的值，您可以这样做：
update A a set a.x = (select b.x from B b where ... and ROWNUM=1)
E.g.
例如
update db1.CENSUS set (notes)
=
(
select notes
from db2.CENSUS cen
where db1.CENSUS.uid = cen.uid
and ROWNUM=1
)
where headcount_ind = 'Y' and capture_FY = '2015'
回答by kevinsky
quick and dirty is
又快又脏是
update db1.CENSUS cen set cen.notes
=
(
select MAX(notes)
from db2.census census
where db1.DW_HRT_PERSONNEL_CENSUS.uid = census.uid
)
where cen.headcount_ind = 'Y' and cen.capture_FY = '2015'
But, this probably does not do what you want. You have more than one note entered by one member of db1.DW_HRT_PERSONNEL_CENSUS.
但是，这可能不会做你想要的。db1.DW_HRT_PERSONNEL_CENSUS 的一名成员输入了多个注释。
In order to prevent this error you must know the relationships between the tables. Is this a one member of db1.DW_HRT_PERSONNEL_CENSUS maps to one note, or, more likely, one member of db1.DW_HRT_PERSONNEL_CENSUS can make many census notes? A more likely solution is:
为了防止此错误，您必须了解表之间的关系。这是 db1.DW_HRT_PERSONNEL_CENSUS 的一个成员映射到一个笔记，或者更有可能的是，db1.DW_HRT_PERSONNEL_CENSUS 的一个成员可以制作多个人口普查笔记？更可能的解决方案是：
CURSOR census_taker IS
SELECT db1.DW_HRT_PERSONNEL_CENSUS.UID
FROM db1.DW_HRT_PERSONNEL_CENSUS;
For each census_taker in db1.DW_HRT_PERSONNEL_CENSUS LOOP
update db1.CENSUS cen set cen.notes
=
( select census.notes
from db2.census census
where db1.DW_HRT_PERSONNEL_CENSUS.uid = census.uid
)
where cen.headcount_ind = 'Y'
and cen.capture_FY = '2015'
and cen.PERSONNEL_CENSUS_UID = census_taker.UID;
END LOOP;
结束循环；
回答by Saagar Elias Hymany
The error means the subquery
错误意味着子查询
select notes
from db2.CENSUS census
where db1.CENSUS.uid = census.uid
is returning more than one row with notes, while the UPDATE statement is expecting a single value to be returned.
返回多行带有注释，而 UPDATE 语句期望返回单个值。
Try the following.
请尝试以下操作。
update db1.CENSUS set (notes) = (select notes
from db2.CENSUS census
inner join db1.CENSUS db1Census on db1Census.uid = census.uid
where db1Census.headcount_ind = 'Y'
and db1Census.capture_FY = '2015')
where headcount_ind = 'Y' and capture_FY = '2015'
This might also give you the same error if there are duplicate entries in db2.CENSUSfor the uidand where headcount_ind = 'Y' and capture_FY = '2015'
如果db2.CENSUStheuid和 where 中存在重复条目，这也可能会给您同样的错误headcount_ind = 'Y' and capture_FY = '2015'
