Hello, SQL> create table my_test_table1 (folder_id number(4), parent_id number(4)); Table created. SQL> desc my_test_table1 Name Null? Type ----------------------------------------- -------- ---------------------------- FOLDER_ID NUMBER(4) PARENT_ID NUMBER(4) SQL> alter table my_test_table1 add ( constraint my_test_table1_pk primary key (folder_id)); Table altered. SQL> alter table my_test_table1 add ( constraint my_test_table1_fk foreign key (parent_id) references my_test_table1 (folder_id)); Table altered. SQL> insert into my_test_table1 values (1, null); 1 row created. SQL> insert into my_test_table1 values (2,1); 1 row created. SQL> insert into my_test_table1 values (3,2); 1 row created. SQL> insert into my_test_table1 values (4,3); 1 row created. SQL> insert into my_test_table1 values (11, null); 1 row created. SQL> select * from my_test_table1; FOLDER_ID PARENT_ID ---------- ---------- 1 2 1 3 2 4 3 11 delete from my_test_table1 where folder_id = 1 * ERROR at line 1: ORA-02292: integrity constraint (UT03200.MY_TEST_TABLE1_FK) violated - child record found SQL> r 1 SELECT LPAD(' ',2*(LEVEL-1)) || folder_id folder_id, parent_id 2 FROM my_test_table1 3 START WITH parent_id is null 4 CONNECT BY PRIOR folder_id = parent_id 5* order by level desc FOLDER_ PARENT_ID ------- ---------- 4 3 3 2 2 1 1 11 1 delete from my_test_table1 2* where exists (SELECT * FROM my_test_table1 START WITH parent_id is null CONNECT BY PRIOR folder_id = parent_id ) 5 rows deleted. HTH On 1/23/06, DBA Deepak <oracle.tutorials@xxxxxxxxx> wrote: > > Hi All, > > Have a table like ... > > FOLDER_ID PARENT_ID > ----------------- --------------- > 001 > 002 001 > 003 002 > 004 003 > 005 004 > > > and so on.... > > The parent_ID is the foreign key referencing FOLDER_ID column of the same > table. > > Want a SQL which deletes the bottom most folder_id i.e., 005 in the above > case, and then 004 and then 003 .... > > Do not want to use cascade delete.... > ** > > > -- > Regards, > > Deepak > Oracle DBA > -- ------------------------------------------------ Enrique