SELECT * FROM FORUM_A_TOPICS t
INNER JOIN FORUM_A_REPLY r
ON r.TOPIC_ID = t.TOPIC_ID
WHERE r.R_DATE < '20031015000000'
Obviously that is a select not a delete, does this look correct. It should delete all archived topics and replies old than 1 year when I replace "SELECT * FROM" with "DELETE"
Your script will almost delete from the FORUM_A_TOPICS table. The reason I say almost, is that you would get a syntax error with the aliasing of the table coming right after the table name in the DELETE FORUM_A_TOPICS t part of it. Also, you can only delete from one table at a time so it will not delete from the FORUM_A_REPLY table.
I modified your script a little for the delete. You might use the getdate() - 365 (second script) so that you don't have to change the date every time you run it, just a thought... Hope this helps!!!
DELETE T FROM FORUM_A_TOPICS T INNER JOIN FORUM_A_REPLY R ON R.TOPIC_ID = T.TOPIC_ID WHERE R.R_DATE < '20031015000000'
DELETE T FROM FORUM_A_TOPICS T INNER JOIN FORUM_A_REPLY R ON R.TOPIC_ID = T.TOPIC_ID WHERE R.R_DATE < getdate() - 365
Topic Locked
Posted - 15 October 2004 : 09:29:27
