UPDATE DIRLEVEL of TABLEA to the value + 1 of the DIRLEVEL of the record that is it's MASTERDIR

Hi,

I have this structure ( create scripts follow below)

DIRID, MASTERDIR, DIRLEVEL

1      1          0
2      1          1
3      1          1
4      3          2
5      4          3
6      5          3
7      5          3
8      7          4
9      7          4
10     7          4

this results in this type of structure.

DIR 1 LEVEL 0
--- DIR 2 LEVEL 1
--- DIR 3 LEVEL 3

• DIR 4 LEVEL 2
  • DIR 5 LEVEL 3
    • DIR 6 LEVEL 3
  • DIR 7 LEVEL 3
    • DIR 8 LEVEL 4
    • DIR 9 LEVEL 4
    • DIR 10 LEVEL 4

Dus : UPDATE DIRLEVEL of TABLEA to the value + 1 of the DIRLEVEL of the record that is it's MASTERDIR

Easy ?

I was hoping this would work, and i think i almost have it.

UPDATE TABLEA SET DIRLEVEL = (select DIRLEVEL from TABLEA where DIRID = (SELECT MASTERDIR from TBLDOCZDIR)) + 1

Here's the structure:

CREATE TABLE "TABLEA"("DIRID" NUMBER, "MASTERDIR" NUMBER,     "DIRLEVEL" NUMBER, UNIQUE("DIRID"));

INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (1 ,1 ,0  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (2 ,1 ,1  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (3 ,1 ,1  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (4 ,3 ,2  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (5 ,4 ,3  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (6 ,5 ,3  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (7 ,5 ,3  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (8 ,7 ,4  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (9 ,7 ,4  )
INSERT INTO "TABLEA" (DIRID ,MASTERDIR ,DIRLEVEL ) VALUES (10 ,7 ,4  )