Tree Structure with Connect BY inner join and update

Question

I have 3 tables. The table Test, Folder and Iteration.

Every test is Linked with a Folder. The Folder, which is linked to the test is a child of many other folders.

From these folders the one on the top of the hierarchy is linked with the table Iteration.

Now I want to Update the Iteration_ID in a Test table. So that I have a "fast" connection between Test and Iteration. This is my try:

    update Test a set a.Iteration_ID =
(nvl((
--The Select Part
 select b.ID from Iteration b inner join Folder c on b.Folder_ID = c.ID 
    where c.ID = 
    (
        select * from 
        (
            SELECT d.ID FROM Folder d START WITH d.ID =  135196 CONNECT BY PRIOR d.parent_id = d.id
            order by LEVEL desc 
        )
        where rownum= 1
    )

--End Select Part
),0));

The Query above works, but I have a static ID at d.ID. I would like to set there a.Folder_ID :

    update Test a set a.Iteration_ID =
(nvl((
--The Select Part
 select b.ID from Iteration b inner join Folder c on b.Folder_ID = c.ID 
    where c.ID = 
    (
        select * from 
        (
            SELECT d.ID FROM Folder d START WITH d.ID =  a.Folder_ID CONNECT BY PRIOR d.parent_id = d.id
            order by LEVEL desc 
        )
        where rownum= 1
    )

--End Select Part
),0));

The Problem is that Oracle doesn't know the a.folder_id

ORA-00904: "A"."FOLDER_ID": ungültiger Bezeichner
00904. 00000 -  "%s: invalid identifier"

Anybody know a better way to solve the problem or to improve the query?

For example to get the root folder withour the select * from and rownum = 1?

Thanks!

score 0 · Answer 1 · answered Nov 01 '12 at 23:13

I'm not sure I got your DB structure right, but I hope my idea will help you in either case. So I suggest providing more information on DB structure, e.g. can there be many Iterations for a Test?

So, here is my proposal:

UPDATE test a set a.Iteration_ID =
(nvl((
 SELECT i.ID
 FROM Iteration i,
  (SELECT id, first_value(id) over(partition by connect_by_root(id)) first_id
   FROM Folder
   START WITH parent_id IS NULL
   CONNECT BY parent_id = PRIOR id) folder_flat
 WHERE a.TEST_ID   = folder_flat.first_id
   and i.Folder_ID = folder_flat.ID 
),0));

General idea is flattening hierarchical structure in subquery so it can be easily joined.

Tree Structure with Connect BY inner join and update

1 Answers1