日期:2014-05-16  浏览次数:20594 次

ORA-01427 单行子查询返回多个行 解决方法
数据库分级结构中,删除叶子节点后需要将其父节点的的状态重新判断是否为叶子节点。根据ORACLE语句可直接查询出所几点是否为叶子节点,SQL语句如下:
SELECT s_entity_code,
NVL((SELECT '0'
  FROM am_sys_entity_code B
  WHERE I.s_entity_code=B.s_parent_code
  AND ROWNUM  < 2),'1') S_MARK_TAIL
  FROM am_sys_entity_code I
 START WITH s_parent_code = '0'
CONNECT BY s_parent_code = PRIOR s_entity_code;


使用update将节点状态更新到S_MARK_TAIL字段:
UPDATE am_sys_entity_code SET S_MARK_TAIL = (SELECT 
NVL((SELECT '0'
  FROM am_sys_entity_code B
  WHERE I.s_entity_code=B.s_parent_code
  AND ROWNUM  < 2),'1') S_MARK_TAIL
  FROM am_sys_entity_code I
 START WITH s_parent_code = '0'
CONNECT BY s_parent_code = PRIOR s_entity_code);


数据库报错:ORA-01427 单行子查询返回多个行。

解决办法为,在update语句中在嵌套一层select查询,并关联主键s_entity_code字段。SQL如下:
UPDATE am_sys_entity_code t1 SET t1.s_mark_tail = (
       select t2.s_mark_tail from (
              SELECT s_entity_code,
                NVL((SELECT '0'
                  FROM am_sys_entity_code B
                  WHERE I.s_entity_code=B.s_parent_code
                  AND ROWNUM  < 2),'1') S_MARK_TAIL
                  FROM am_sys_entity_code I
                 START WITH s_parent_code = '0'
                CONNECT BY s_parent_code = PRIOR s_entity_code
       ) t2 where t2.s_entity_code = t1.s_entity_code
);