时间：2021-07-01 10:21:17 帮助过：9人阅读
oracle 同一张表 数据是树形结构
fid(主键) parentid(父ID) fpath(节点全路径)
1 1
2 1 1/2
3 1 1/3
4 2 1/2/4
但是现有数据是用excel导入的 fpath 是空值，现在想写条语句把 fpath全都加上，求语句
建表及数百据
| 1 2 3 4 5 6 7 8 9 10 11 |
create table test
(fid int,
parentid int,
fpath varchar2(100));
insert into test values (1,null,null);
insert into test values (2,1,null);
insert into test values (3,1,null);
insert into test values (4,2,null);
|
执行更度新版语句
| 1 2 3 4 5 6 7 8 |
update test a set a.fpath=
(select b.fpath from
(select fid,parentid,
substr(sys_connect_by_path(fid,‘/‘),2) fpath
from test
start with fid=1
connect by prior fid=parentid) b
where a.fid=b.fid);
|
效果截图权
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE TABLE temp (
fid int,
parentid int,
fpath varchar(20)
);
insert into temp (fid, parentid) values(1, null);
insert into temp (fid, parentid) values(2, 1);
insert into temp (fid, parentid) values(3, 1);
insert into temp (fid, parentid) values(4, 2);
update temp
set
fpath = (
SELECT fpath from
(
SELECT tt.fid, TRIM(LEADING ‘/‘ FROM SYS_CONNECT_BY_PATH(tt.fid, ‘/‘)) as fpath from temp tt
START WITH tt.parentid is null
CONNECT BY PRIOR tt.fid = tt.parentid
) t
where t.fid = temp.fid
);
已更新e68a84e8a2ad7a64313333326432354行。
SQL> select * from temp;
FID PARENTID FPATH
---------- ---------- ----------------------------------------
1 1
2 1 1/2
3 1 1/3
4 2 1/2/4
|
oracle树形数据级联更新
标签：opener line 空值 process nta oop ali start path