我执行一个SQL，
update irpt_departments a set (a.dept_code,a.dept_class,a.tx_dz)=
(select b.dept_code,b.dept_class,b.tx_dz from irpt_departments b where a.Id=b.parent and b.isjc='0' And b.caption Like '%基层户')
where exists (select 1 from irpt_departments b where a.Id=b.parent and b.isjc='0' and b.caption Like '%基层户' ) And a.isjc='9';
有的地方执行没事，有的地方执行提示
ORA-01427: 单行子查询返回多个行 这个问题怎么解决呢？
多谢：）
------解决方案--------------------
改变你查询的条件，或者说你的查询逻辑思路，
在 set (a.dept_code,a.dept_class,a.tx_dz)=....这里查询返回了多个值，当然没法用=去匹配。
------解决方案--------------------
在你的查询结果里针对a.Id=b.parent 这个条件select b.dept_code,b.dept_class,b.tx_dz from irpt_departments b where a.Id=b.parent and b.isjc='0' And b.caption Like '%基层户'返回多个值 可以先检查一下你的查询表
------解决方案--------------------
单行子查询返回多个行.......这说明你的
select b.dept_code,b.dept_class,b.tx_dz from irpt_departments b where a.Id=b.parent and b.isjc='0' And b.caption Like '%基层户')
where exists (select 1 from irpt_departments b where a.Id=b.parent and b.isjc='0' and b.caption Like '%基层户' ) And a.isjc='9'有多个值.....这当然不行...
------解决方案--------------------
- SQL code
改成下面的试试:UPDATE IRPT_DEPARTMENTS A SET (A.DEPT_CODE, A.DEPT_CLASS, A.TX_DZ) = (SELECT B.DEPT_CODE, B.DEPT_CLASS, B.TX_DZ FROM IRPT_DEPARTMENTS B WHERE A.ID = B.PARENT AND B.ISJC = '0' AND B.CAPTION LIKE '%基层户' AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM IRPT_DEPARTMENTS B WHERE A.ID = B.PARENT AND B.ISJC = '0' AND B.CAPTION LIKE '%基层户' AND ROWNUM = 1) AND A.ISJC = '9';