sqloracle-databasehierarchical-data

Inserting root id to all its children

I have a table with hierarchy v_name

**id:  name:  child_id** 
1000   abc      null
1001   bac      1000
1002   cab      1001
1003   lop      1001
1004   jui      null
1005   njj      1004

Now we are pulling this with connect by root. As the data increased the search is taking 25 secs to execute. Now we need to add one root_id in the table where the root id would be top parent id in the Hierarchy. Like below.

**id:  name:  child_id** root_id
1000   abc      null      1000
1001   bac      1000      1000
1002   cab      1001      1000
1003   lop      1001      1000
1004   jui      null      1004
1005   njj      1004      1004

I tried to write a script to insert it. But failed.

INSERT INTO v_name SET root_id = 
SELECT id, LEVEL
      FROM v_name
      START WITH id = null
      CONNECT BY PRIOR id = child_id
      ORDER SIBLINGS BY child_id;

Please help.

Solution

  • You (correct) SELECT may be:

    SELECT id, name, child_id, connect_by_root id root_id, LEVEL
      FROM v_name
      START WITH child_id IS NULL
      CONNECT BY PRIOR id = child_id
      ORDER SIBLINGS BY id;

    Your UPDATE function should then be a MERGE:

    MERGE INTO v_name
USING
(
  SELECT id, name, child_id, connect_by_root id root_id, LEVEL
      FROM v_name
      START WITH child_id IS NULL
      CONNECT BY PRIOR id = child_id
      ORDER SIBLINGS BY id
) u
ON (u.id = v_name.id)
WHEN MATCHED THEN UPDATE SET v_name.root_id = u.root_id;

    See that all running in a DBFiddle.