Re: Database Actions - Multiple Deletion

[Leon Widdershoven]

Easiest is to make 3 tables:
table 1 Posts: Column id, Column Post
table 2 Comments: Column id, Column Comment
table 3 Combination: Column id, Column PostID, column CommentID
When deleting a comment, (and all references to it):

DELETE FROM Comments WHERE ID = <request:get-parameter name="commentID"/>;
DELETE FROM Combination WHERE CommentID = <request:get-parameter 
name="commentID"/>

You might also wish to delete all posts which are only related to that 
comment:
DELETE FROM Posts WHERE id NOT IN ( SELECT PostID FROM Combination);

This is an example with untested SQL. The trick is to have the two 
tables relate to each other using a
third table. All tables can now have a unique, autoincremented ID which 
makes it quite easy to
process.

If you delete a Post:
DELETE FROM Posts WHERE ID=<request:get-parameter name="postID"/>;
DELETE FROM Conbination WHERE PostID=<request:get-parameter name="postID"/>;
And maybe also cleaning up.
This is a very normal way to relate tables with non-unique relations.
Leon

Davide wrote:

Hi all i'm having a problem.

I've a db with 2 tables, one takes posts and one other takes comments. 
there can be more comments related to a post, so when i delete a row 
form posts i want to delete every related comment from comments. How 
to do?

this is the descriptor i use to delete a post:

<Posts>
    <connection>mcblogger</connection>
    <table name="Posts" alias="Posts">
        <keys>
            <key name="Post_ID" type="int" autoincrement="true">
                <mode name="auto"  type="autoincr"/>
            </key>
        </keys>
    </table>
</Posts>
and it run. How to delete also related comments from table comment?
to delete a comment i use this descriptor:
<Comments>
    <connection>mcblogger</connection>
    <table name="Comments" alias="Comments">
        <keys>
            <key name="Comment_ID" type="int" autoincrement="true">
                <mode name="auto"  type="autoincr"/>
            </key>
        </keys>
    </table>
</Comments>
i tried this but it doesn't run:

    <table name="Posts" alias="Posts">
        <keys>
            <key name="Post_ID" type="int" autoincrement="true">
                <mode name="auto"  type="autoincr"/>
            </key>
        </keys>
    </table>
    
    <table name="Comments" alias="Comments">
        <keys>
            <key name="Comment_ID" type="int" set="slave">
            </key>
        </keys>
        <values>
            <value name="Post_ID" type="int" set="master"/>
        </values>
    </table>

how to do??

Thanks all, Best Regards, Davide



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]