[thelist] Message Archive - MySQL

[Chris Marsh]

Dear all

I have a database that contains a table for messages that have been sent
between application users. Each message has a time stamp set when
received. When a user logs on, I need to check to see which messages are
more than 30 days old and get rid of them. This is no problem, and I
believe the following will achieve exactly what I want:

DELETE FROM message WHERE touid = '12345' AND (TO_DAYS(Now()) -
TO_DAYS(date)) > 30

However, although the client seems sure that no one will *ever* want to
recover a message more than 30 days old, I do not share his faith.
Consequently, what I want to do is to create a table with the same
structure as 'message' called 'message_archive'. Instead of immediately
deleting, I want to find each pertinent record, insert the data into
'message_archive', then delete the record from 'message (as above). The
application is ASP (VBScript), and the database is MySQL. Can anyone
assist or at least prod me in the right direction?

Many thanks in advance...

Regards

Chris Marsh