SEO

Hi all,

Can anyone solve my problem..

i have a table in which the foreign key referance is to another column
in the same table.

Now is there any query to delete both the records the child and the
parent using a single sql query.

the table stucture is as follows

ID, FDB_ID, STD_ID, etc

here FDB_ID id the foreign key for ID


ID FDB_ID ROLL REG_NO ST CD
38 37 364 929560 491 S
37 364 929560 491 F

the second record is the parent and the first is the child

Now i need to delete both the records using a single sql query. Can you
pleas give the query??

On 28 Aug 2006 05:52:47 -0700, swadeep.c@gmail.com wrote:

>i have a table in which the foreign key referance is to another column
>in the same table.
>
>Now is there any query to delete both the records the child and the
>parent using a single sql query.
>
>the table stucture is as follows
>
>ID, FDB_ID, STD_ID, etc
>
>here FDB_ID id the foreign key for ID
>
>ID FDB_ID ROLL REG_NO ST CD
>38 37 364 929560 491 S
>37 364 929560 491 F
>
>the second record is the parent and the first is the child
>
>Now i need to delete both the records using a single sql query. Can you
>pleas give the query??

DELETE FROM tab WHERE ID IN (37, 38);

Or set ON DELETE CASCADE on the foreign key and delete just the parent.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Hi,

One idea would be an ON DELETE CASCADE constraint on the table. See
below for a simple test case...

SQL> create table t0828(c number, d number);

Table created.

SQL> alter table t0828 add constraint t0828_pk primary key(c);

Table altered.

SQL> alter table t0828 add constraint t0828_fk foreign key(d)
references t0828(c);

Table altered.

SQL> insert into t0828 values(1,1);

1 row created.

SQL> insert into t0828 values(2,1);

1 row created.

SQL> insert into t0828 values(3,null);

1 row created.

SQL> insert into t0828 values(4,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t0828;

C D
---------- ----------
2 1
3
4 3
1 1

SQL> delete from t0828 where c = 3;
delete from t0828 where c = 3
*
ERROR at line 1:
ORA-02292: integrity constraint (REP.T0828_FK) violated - child record
found


SQL> alter table t0828 drop constraint t0828_fk;

Table altered.

SQL> alter table t0828 add constraint t0828_fk foreign key(d)
references t0828(c) on delete cascade;

Table altered.

SQL> delete from t0828 where c = 3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t0828;

C D
---------- ----------
1 1
2 1

SQL>

After we add the constraint with the clause noted above, we can delete
with no extra SQL.

Regards,

Steve