Deleteing a Message and its replies from a message board. 1

[jewel464g]

I have a message board and would like to delete all messages that are older than 2 months. I also want the replies to that message deleted at the same time. The problem I have is deleting the replies. I know that to delete messages over 2 months I would just write(pseudocode):
DELETE FROM table
WHERE date > 2 months

But the replies won't necessarily be over 2 months old. Is there someway to group the original message with its replies? Here are the entries in my table.
ID - Autonumber
MessageId
DateOfPost
Subject
Message

Messages are assigned a MessageId like so:
Original message is 0001
reply to this message would be 0001.01
a second reply to the original mess. would be 0001.02
a third 0001.03, etc.
a reply to the first reply would be 0001.01.01 and on down the line.

I'm new at SQL so please be as descriptive as possible in your answer. Much Thanks.
Jessica

 

[Databaseguy]

You'll need two queries. Create a new Query called Query2 and use this SQL Statement

SELECT Left([MessageID],4) AS Expr1, tblMessage.DateofPost
FROM tblMessage
WHERE (((tblMessage.DateofPost)<Now()-&quot;60&quot);

In your second Query, use this SQL statement

DELETE Left([tblMessage].[MessageID],4) AS Expr2, tblMessage.ID
FROM Query2 INNER JOIN tblMessage ON Query2.MessageID = tblMessage.MessageID
WHERE (((Left([tblMessage].[MessageID],4))=[query2].[Expr1]));

 

[jewel464g]

I put that in like you suggested but I'm getting an error: Specify the table containing the records you want to delete.

 

[Databaseguy]

OK, you taught me something new. When performong a delete query with koined tables, ya gotta include the Select Distinct keyword.

Heres' the SQL for Query2


SELECT Left([MessageID],4) AS Expr1, [tblMessage].[DateofPost], [tblMessage].[MessageID]
FROM tblMessage
WHERE ((([tblMessage].[DateofPost])<Now()-&quot;60&quot);


And the SQL for the other query.


DELETE DISTINCTROW Left([tblMessage].[MessageID],4) AS Expr2, tblMessage.*
FROM Query2 INNER JOIN tblMessage ON Query2.MessageID = tblMessage.MessageID
WHERE (((Left([tblMessage].[MessageID],4))=[query2].[Expr1]));

I ran it, and it deleted the records.

 

[jewel464g]

Thanks, everything is working great now.

 

[Databaseguy]

Yeah, you taught me something new too. Thx.