Re: Query Help...

  • From: Enrique Fernandez-Pampillon <oracle.pampillon@xxxxxxxxx>
  • To: oracle.tutorials@xxxxxxxxx
  • Date: Mon, 23 Jan 2006 11:06:31 +0100

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

Other related posts: