sql - ORA-01427:单行子查询在CASE语句中返回多个行

Question

我收到以下代码的ORA-01427错误：

update rpt_group a
set a.rpt_category_id = 
case
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '01') then '201'
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '02') then '202'
  when ((select c.control from grpmisc c
 where (c.grp = a.grp)) = '03') then '203'
 else '93'
   end
where rpt_category_id = '93';

但是当我简单地做时，说

select c.control from grpmisc c, rpt_group a
 where c.grp = a.grp and a.grp = '01'

它不返回任何行。 '02'和'03'相同。那么，为什么会出现“子查询返回多个行”错误？

谢谢..

Answer 1

您正在运行此测试...
select c.control from grpmisc c, rpt_group a where c.grp = a.grp and a.grp = '01'

但是您的子查询是在此形成的...
select c.control from grpmisc c, rpt_group a where c.grp = a.grp

如果您运行两个查询中的后者，我敢打赌您会得到很多记录？这意味着要么您从子查询中丢失了and a.grp = '01'，要么您需要将结果限制为一个记录...


几个选择可能是...

(select c.control from grpmisc c where c.grp = a.grp group by c.control)

(select c.control from grpmisc c where c.grp = a.grp and rownum < 2 order by <blah>)