Record Deletion



IBM's flagship relational database management system

Record Deletion

Postby atodpquery » Wed Apr 22, 2009 5:51 pm

Hello,

I read a cobol porgram recently and it deletes the parent record in table and commenting the linked child records will be automatically deleted by RI. Is that true or does it happen upon how you define the table structure. As I know before that all child records should be deleted before the parent record to stop violate the RI relationship.

Michelle :oops:
atodpquery
 
Posts: 31
Joined: Sat Nov 15, 2008 3:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Record Deletion

Postby dick scherrer » Thu Apr 23, 2009 3:21 am

Hello,

When a table is created, it is possible to specify "on delete cascade".

Look here:
http://publibz.boulder.ibm.com/cgi-bin/ ... T#FIRSTHIT
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Record Deletion

Postby atodpquery » Fri May 01, 2009 4:13 pm

Thank you very much that explains my puzzle.

However, I found some orphan child and I want to delete now . when I delete with record key and it allows me to delete. However, if I delete with a subquery it will not allows me to delete. Is it feasible?

example:

delete record from table a
where key = ccc
This works fine but needs manual manipulation.

automatic retrieve records like:

delete records from table a
where not exists
(select 1 from table b
where a.key = b.key)
this does not allows me to delete.

:roll: :lol: :oops:
atodpquery
 
Posts: 31
Joined: Sat Nov 15, 2008 3:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Record Deletion

Postby dick scherrer » Fri May 01, 2009 9:19 pm

Hello,

Suggest you identify the orphans in one query and store the keys and then delete them in another. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post