Page 1 of 1

Record Deletion

PostPosted: Wed Apr 22, 2009 5:51 pm
by atodpquery
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:

Re: Record Deletion

PostPosted: Thu Apr 23, 2009 3:21 am
by dick scherrer
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

Re: Record Deletion

PostPosted: Fri May 01, 2009 4:13 pm
by atodpquery
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:

Re: Record Deletion

PostPosted: Fri May 01, 2009 9:19 pm
by dick scherrer
Hello,

Suggest you identify the orphans in one query and store the keys and then delete them in another. . .