What I say will not help you since this is a MySQL limitation. However, on
more fully-featured SQL systems, you can specify a referential constraint that
will delete child rows when the parent row is deleted.
You might have something like this on table C (assumes table A is the master):
create table c (
groupno int not null
constraint cgroup_ref
references a
on update cascade
on delete cascade,
otherfield...
This creates a referential constraint between tables A and C. Now, what
happens is you cannot even create a new groupno in C until it exists in A.
This assures the database does not have orphaned child rows.
Then, when you "delete from A where groupno=1", the groupno delete will
propagate downward to all tables that have a referential constraint of DELETE
CASCADE automatically. (This is handled by the database engine, not the
application.)
One question I have about your table diagram below is: are tables A and B
really joint owners of the groupno? It would be even better if a SINGLE table
was the "master" that owned the groupno. When/if MySQL offers referential
integrity, you could take advantage of the above.
Don't get me wrong, I'm not disparaging MySQL. I use it on my personal website
and dabbling with web applications with it and PHP. But, I work with 2 other
database systems on a daily basis (DB2 and MS-SQL Server) that allow the above
solution to your problem.
HTH,
Loyd
On Tue, 25 Sep 2001 09:09:31 -0700, "Adams, Bill TQO" <[EMAIL PROTECTED]> wrote:
>In MySQL the best (only?) solution I know of is to write a script (e.g. in
>perl or python) that will traverse the tree for you.
>
>e.g:
>
>select a_keys from A
>foreach key ( a_keys ){
> select c_keys from B
> ... etc.
>
> delete from b where key in ( a_keys )
>
>If you were really looking for punishment you could create a table of
>meta-data that descibed how the tables were related and then use that to do
>the cascade delete. And of course, LEFT JOIN is your friend for finding
>reocrds in lower tables that do not have a matching reocrod in the parent
>table, e.g.: SELECT b_keys FROM B LEFT JOIN A on ( common_key ) where
>A.common_key IS NULL;.
>
>--Bill
>
>
>½ÉÃ溸 wrote:
>
>> Hi.
>> I came across serious problem.
>> There is hierarchy among groups listed below like directory structure.
>>
>> A(group)---------B(group)
>> |
>> -----C(group) --------D(group)
>> |
>> ----E(group)
>> |
>> ----F(group)
>>
>> I want to delete A group. This requires deletion of all child groups.
>> Above figure is a little simple but if this tree grows large that's my
>> problem.
>>
>> Would you please tell me the answer?
>>
>> For reference table is like this.
>>
>> Field Type
>> groupname varchar(50)
>> groupno int
>> p_groupno int <- This means parent group no.
>>
>> Thanks in advance!!
>>
>> ---------------------------------------------------------------------
>> Before posting, please check:
>> http://www.mysql.com/manual.php (the manual)
>> http://lists.mysql.com/ (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
"How much would you pay for your life?"
"More than I would take to give it up."
[EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
