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.

Answer 1

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