Assign value based on another

There are 2 input tables, objective is to assign value in Table 1 based on Previous ID & Next ID mapping present in Table 2

Edited Input Table 1:

ID	value
123	null
456	null
789	maths
246	english
135	chemistry
76899	null

Edited Input Table 2:

Previous ID	Next ID
246	123
135	456
123	123
456	456

Edited Output:

ID	Value
123	english
456	chemitry
789	maths
246	english
135	chemistry
76899	null

For example, for ID 123, it's previous ID 246 has a value of english in Table 2, hence the same should be assigned to ID 123 in value column of Table 1

Edit: It would good to have a select based solution instead of Update/Merge, thanks!

Solution

You can use an OUTER JOIN:

SELECT t1.id,
       COALESCE(p.value, t1.value) AS value
FROM   table_1 t1
       LEFT OUTER JOIN (
         table_2 t2
         INNER JOIN table_1 p
         ON (t2.previous_id = p.id)
       )
       ON (   t1.id = t2.next_id
          AND t2.previous_id != t2.next_id )

or:

SELECT t1.id,
       COALESCE(p.value, t1.value) AS value
FROM   table_2 t2
       INNER JOIN table_1 p
       ON (t2.previous_id = p.id)
       RIGHT OUTER JOIN table_1 t1
       ON (   t1.id = t2.next_id
          AND t2.previous_id != t2.next_id )

or a correlated sub-query:

SELECT t1.id,
       COALESCE(
         ( SELECT p.value
           FROM   table_2 t2
                  INNER JOIN table_1 p
                  ON (t2.previous_id = p.id)
           WHERE  t1.id = t2.next_id
           AND    t2.previous_id != t2.next_id
         ),
         t1.value
       ) AS value
FROM   table_1 t1

Or, if you can have multiple levels of previous_id/next_id pairs then you can use a hierarchical query:

SELECT t1.id,
       COALESCE(
         ( SELECT p.value
           FROM   table_2 t2
                  INNER JOIN table_1 p
                  ON (t2.previous_id = p.id)
           WHERE  CONNECT_BY_ISLEAF = 1
           START WITH
                  t1.id = t2.next_id
           AND    t2.previous_id != t2.next_id   
           CONNECT BY NOCYCLE
                  PRIOR t2.previous_id = t2.next_id
           AND    t2.previous_id != t2.next_id
         ),
         t1.value
       ) AS value
FROM   table_1 t1

Which, for the sample data:

CREATE TABLE table_1 (ID, value) AS
SELECT 123, null FROM DUAL UNION ALL
SELECT 456, null FROM DUAL UNION ALL
SELECT 76899, null FROM DUAL UNION ALL
SELECT 789, 'maths' FROM DUAL UNION ALL
SELECT 246, 'english' FROM DUAL UNION ALL
SELECT 1234, 'physics' FROM DUAL UNION ALL
SELECT 6789, 'biology' FROM DUAL UNION ALL
SELECT 135, 'chemistry' FROM DUAL;

CREATE TABLE Table_2 (Previous_ID, Next_ID) AS
SELECT 246, 123 FROM DUAL UNION ALL
SELECT 135, 456 FROM DUAL UNION ALL
SELECT 123, 123 FROM DUAL UNION ALL
SELECT 1234, 6789 FROM DUAL UNION ALL
SELECT 6789, 6789 FROM DUAL UNION ALL
SELECT 456, 456 FROM DUAL;

All output:

ID	VALUE
123	english
456	chemistry
76899	null
789	maths
246	english
1234	physics
6789	physics
135	chemistry

If you want to UPDATE the table (rather than just SELECTing from the table) then you can use either of the correlated sub-queries from my 3rd or 4th suggestions and use that in the UPDATE. For example:

UPDATE table_1 t1
SET value = COALESCE(
              ( SELECT p.value
                FROM   table_2 t2
                       INNER JOIN table_1 p
                       ON (t2.previous_id = p.id)
                WHERE  CONNECT_BY_ISLEAF = 1
                START WITH
                       t1.id = t2.next_id
                AND    t2.previous_id != t2.next_id   
                CONNECT BY NOCYCLE
                       PRIOR t2.previous_id = t2.next_id
                AND    t2.previous_id != t2.next_id
              ),
              t1.value
            );

Would update the table even when there are multiple levels of indirection in table_2.

fiddle