Re: Query Help...

From: Enrique Fernandez-Pampillon <oracle.pampillon_at_gmail.com>
Date: Mon, 23 Jan 2006 11:06:31 +0100
Message-ID: <7ba974f50601230206t63a4697amf4b73ed7ece32a11@mail.gmail.com>

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 23 2006 - 04:06:31 CST