此问题已在此处有答案：

how to get the root ancestors in a hierarchy query using oracle-10g?（2个答案）
3个月前关闭。

PARENT_AC            CHILD_AC
100         101
101         102
102         103
200         201
201         202
300         301

我需要如下更新ACCT_TBL. PACT_ACC_NO
ACCT_TBL有数百万条记录。可以使用MERGE语句吗？

ACC_NO         PARENT_ACC_NO
-------------------------------
100              100
103              103
202              200
300              300
301              300

我需要找到根父它可能有1级/ 2级/3级或第N级。如果我想知道103的父母帐户，那么我的结果应该是100。

• 注意：这里父帐户永远不会为空 *

请帮帮我

select * from (
  SELECT 
      CONNECT_BY_ROOT t1.CHILD_AC AS CHILD_AC,
      t1.CHILD_AC AS ROOT_AC,
      level AS level_ 
  FROM 
      LONSUB t1
  WHERE  1=1-- t1.PARENT_AC is null
  CONNECT BY t1.CHILD_AC = PRIOR t1.PARENT_AC
  )
  where CHILD_AC='000220245398'
 ;

Im使用update语句将父访问号填充到使用TEST表的另一个表（TAB 1）。

update TAB1 UPD
   SET (UPD.parent_acct_no, UPD.priority_code) = ( SELECT  substr(code  ,1, instr(code, '-',1) -1) parent_ac 
                                                                            ,substr(code  , instr(code, '-',1)+1 ) priority_code
                                                                    FROM  (SELECT nvl((SELECT parent_ac||'-'|| priority_code  
                                                                                         FROM (SELECT parent_ac ,priority_code 
                                                                                                 FROM ( SELECT sub.parent_ac, src.priority_code
                                                                                                          FROM test sub , TAB1 src 
                                                                                                         WHERE sub.parent_ac  = src.V_account_number
                                                                                                           AND CONNECT_BY_ISLEAF = 1
                                                                                                    CONNECT BY sub.child_ac = PRIOR sub.parent_ac
                                                                                                    START WITH sub.child_ac = UPD.v_account_number
                                                                                                      ORDER BY decode(src.priority_code , 'DSC', 1, 'SC', 2, 3 ) ) T
                                                                                                WHERE 1=1
                                                                                                  AND decode(UPD.priority_code , 'DSC', 1, 'SC', 2, 3 )  > decode(T.priority_code , 'DSC', 1, 'SC', 2, 3 ) 
                                                                                                  AND ROWNUM=1) )
                                                                                        ,UPD.v_account_number||'-'||UPD.priority_code ) code
                                                                              FROM dual)
                                                                )

;
我得到以下错误ORA-01436: CONNECT BY loop in user data，但如果我限制为100行更新没有错误。此外，TAB1将有数百万数据需要更新。