I have a table as shown below

I want to update the table by setting the latest_id column value to lowest hierarchical value, which will look like this:

I have tried to use connect by, but the query is getting too complicated as start with cannot have a static value assigned, this update is for the entire table.

Below is what I could write for a single record based on it's id, how can I generalize it for all records in the table?

UPDATE TABLENAME1 
   SET LATEST_ID = (SELECT MAX(ID)
                      FROM TABLENAME1
                     START WITH ID = 3
                   CONNECT BY PREVIOUS_ID = PRIOR ID );

CodePudding user response：

You can use a correlated hierarchical query and filter to get the leaf rows:

UPDATE table_name t
SET latest_id = (SELECT id
                 FROM   table_name h
                 WHERE  CONNECT_BY_ISLEAF = 1
                 START WITH h.id = t.id
                 CONNECT BY previous_id = PRIOR id);

Which, for the sample data:

CREATE TABLE table_name (id, previous_id, latest_id) AS
SELECT 1, null, CAST(null AS NUMBER) FROM DUAL UNION ALL
SELECT 2, 1,    null FROM DUAL UNION ALL
SELECT 3, 2,    null FROM DUAL UNION ALL
SELECT 4, null, null FROM DUAL UNION ALL
SELECT 5, 4,    null FROM DUAL UNION ALL
SELECT 6, 5,    null FROM DUAL;

Updates the table to:

ID	PREVIOUS_ID	LATEST_ID
1	null	3
2	1	3
3	2	3
4	null	6
5	4	6
6	5	6

db<>fiddle here

CodePudding user response：

To the accepted answer, I will add this alterative which might perform better for large datasets by eliminating the correlated subquery.

MERGE INTO table_name t
USING (  
  SELECT      CONNECT_BY_ROOT(id) root_id, id latest_id
  FROM        table_name
  WHERE       connect_by_isleaf = 1
  CONNECT BY  previous_id = prior id ) u
ON ( t.id = u.root_id )
WHEN MATCHED THEN UPDATE SET t.latest_id = u.latest_id;