 <p>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 <code>Iterations</code> for a <code>Test</code>?</p> <p>So, here is my proposal:</p> <pre><code>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)); </code></pre> <p>General idea is flattening hierarchical structure in subquery so it can be easily joined.</p>