Re: Deleting Old MySql Records With PERL

[chacham]

DELETE FROM table WHERE DATE (NOW (), date) >30

That's a terrible approach; it evaluates the expression for each record.

DELETE FROM table WHERE DATE < DATE_SUB(NOW(), INTERVAL 30 DAY)

That is the correct approach. Evaluate the expression just once, and do a simple comparison.

Run the statement from the console to determine if it is working. If it is not, break it apart and figure out why. Obviously, "DATE_SUB(NOW(), INTERVAL 30 DAY)" should be checked to work. Look at the documentation if you do not understand DATE_SUB()

After you are clear that it should work, try it within your script, where the issues would mainly be connectivity or rights related, having already solved any syntactical or type issues.

[Milti]

Thanks for all the feedback. I believe I now have it figured out.

I added a column to the table and named it DateAdded with Type - TIMESTAMP, NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. The comparison you suggested now seems to work in that it does not delete rows that were placed in the table today. After a couple of days I will change the INTERVAL to 2 or 3 days and see what happens when I try the query in the Mysql client. I still have a column named 'Posted' which contains a date in simple English, i.e. Month Day, Year.