|
|
Posted by Hilarion on 10/10/84 11:25
Anon <Ao@anonc.com> wrote:
> Im wondering if someone could help me with a recursive style sql command
>
> section = SectionID, parent
>
> I want to be able to delete a row where SectionID=X but also delete any
> section that matches SectionID.
>
> Kinda like the hierarchy of folders on your harddrive.
>
> I want to delete any subfolders of the main folder I am deleting.
>
> Can anyone give me some direction, my SQL isn't up to all that much.
>
> Ive got the following but its more pseudo code rather than something I
> expect to work.
>
>
> DELETE *
> FROM jss_sections
> WHERE Parent=SectionID AND (DELETE * FROM jss_sections WHERE
> SectionID.new=Parent)
You should add "FOREIGN KEY" constraint to this table and specify
"ON DELETE CASCADE". If you do it, then the SQL server will delete
all dependant records for you.
ALTER TABLE jss_sections
ADD CONSTRAINT FK_jss_sections_parent
FOREIGN KEY (Parent) REFERENCES jss_sections (SectionID)
ON DELETE CASCADE;
Before creating this constraint you have to make sure that all
"Parent" values refer to existing "SectionID" values or are
set to NULL.
Unfortunately not all SQL engines support "ON DELETE CASCADE" and
some of those that do, do not support it on FOREIGN KEYs which
refer to the same table on which they are defined (because of
potential loops). In that case you'll have to write some
recursive procedure (in database or in the application). In PHP
you could do it like that (used preorder to visit==delete
all child records):
function delete_record( $SectionID )
{
$qry = 'SELECT SectionID FROM jss_sections WHERE Parent = ' . $SectionID;
$result = perform_query_on_database_and_return_tabular_results( $qry );
foreach( $row in $result )
{
if (!delete_record( $row['SectionID'] ))
return false;
}
$qry = 'DELETE FROM jss_sections WHERE Parent = ' . $SectionID;
$result = perform_query_on_database_and_return_boolean_result( $qry );
// Throw exception or raise error or do something else informing
// about problem here.
return $result;
}
Hilarion
Navigation:
[Reply to this message]
|