构造类似样例语法执行无问题，可兼容Oracle，根据报错应该是其它部分报错，非该语句，可提供详细定义脚本和数据，做进一步排查？

使用临时表分步处理

-- 步骤1：创建临时表存储层级查询结果 
CREATE GLOBAL TEMPORARY TABLE TEMP_ADDRESS_MAPPING AS 
SELECT X.ID AS RP_ID, B.ID AS ADDRESS_ID_7, B.OLD_SYS_ID AS OLD_SYS_ID_7 
FROM RP_ADDRESS X 
JOIN RM_ADDRESS B ON 1=1 
WHERE B.ADDRESS_TYPE_ID = 80526995 
CONNECT BY NOCYCLE PRIOR B.PARENT_ID = B.ID 
START WITH B.ID = X.ID;
 
-- 步骤2：使用临时表更新目标表 
UPDATE RP_ADDRESS X 
SET (X.ADDRESS_ID_7, X.OLD_SYS_ID_7) = 
(SELECT Y.ADDRESS_ID_7, Y.OLD_SYS_ID_7 
 FROM TEMP_ADDRESS_MAPPING Y 
 WHERE Y.RP_ID = X.ID);
 
-- 步骤3：删除临时表 
DROP TABLE TEMP_ADDRESS_MAPPING;

改一下写法测试下看看

UPDATE RP_ADDRESS X
   SET (X.ADDRESS_ID_7, X.OLD_SYS_ID_7) =
          (SELECT ID, OLD_SYS_ID 
             FROM (SELECT B.ID, B.OLD_SYS_ID 
                     FROM RM_ADDRESS B 
                    WHERE B.ADDRESS_TYPE_ID = 80526995 
                      AND ROWNUM = 1 
                  CONNECT BY NOCYCLE PRIOR B.PARENT_ID = B.ID 
                    START WITH B.ID = X.ID
                  )
          )

也就是在原有CONNECT BY子查询外包一层查询