如何解决ORA-01427错误(单行子查询返回多个行)? [英] How can i resolve an ORA-01427 error (Single-row subquery returns more than one row)?

问题描述

我的查询不断返回ORA-01427错误，但我不知道该如何解决.

My query keeps returning an ORA-01427 error and I don't know how to resolve it.

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'

推荐答案

您收到此错误，因为db2.CENSUS中至少存在uid值存在多行. (可能还有更多.)您可以通过执行以下操作找出uid的哪些值导致了该问题:

You are getting the error because there exists more than one row in db2.CENSUS for at least value of uid. (There could be more.) You can figure out which values of uid are causing the issue by doing the following:

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';

此外，按照上面的方式进行查询，如果对于db1.CENSUS.uid的某些值，如果db2.CENSUS中没有对应的notes值，则db1.CENSUS.notes将设置为NULL.也许这就是您想要的行为?如果没有，您将需要以下内容:

In addition, with your query the way it is above, if there is not a corresponding value of notes in db2.CENSUS for some value of db1.CENSUS.uid, db1.CENSUS.notes will be set to NULL. Maybe that's the behavior you want? If not, you'll want something like the following:

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 );

这篇关于如何解决ORA-01427错误(单行子查询返回多个行)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！