Oracle update value of a table using inner join with the same table

Question

I am trying to update the depth column of bplustree table using this statement,which works fine on MYSQL,but on oracle I have this error:

BEGIN    
  WHILE EXISTS (SELECT * FROM bplustree WHERE depth IS NULL) 
  LOOP    
    UPDATE T SET T.depth = P.depth + 1
        FROM bplustree AS T INNER JOIN bplustree AS P 
        ON (T.parent_node_id = P.node_id) 
    WHERE P.depth >= 0 AND T.depth IS NULL;
  END LOOP;
END;

Error report -
ORA-06550: line 4, column 3:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

Oracle does not support a `FROM` clause in `UPDATE`, so I have no idea what you are trying to do. — Gordon Linoff, Jan 07 '18 at 20:45
I am trying to calculate depth for each node in tree by putting a loop to run through all nodes of the tree @GordonLinoff — Anca Ioana, Jan 07 '18 at 20:47

score 1 · Accepted Answer · answered Jan 07 '18 at 21:50

You may combine update and select by the help of merge like the following statement :

MERGE INTO bplustree t1
USING
(
  SELECT P.depth + 1 depth, p.node_id
    FROM bplustree T INNER JOIN bplustree P
      ON (T.parent_node_id = P.node_id)    
    WHERE P.depth >= 0 AND T.depth IS NULL 
) t2
ON ( t1.node_id = t2.node_id )
WHEN MATCHED THEN UPDATE SET
t1.depth = t2.depth;

D e m o

Oracle update value of a table using inner join with the same table

1 Answers1