It's a database, not a scripting language ... :) You can run a simple cron entry like this:
0 4 * * * /path/to/mysql -u USER -pPASS -D DATABASE -e "delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) >= 30 and status != 'Y';" so at 4 AM each day your SQL would be executed. For long SQL, you can write it to a file and do something like so: 0 4 * * * /path/to/mysql -u USER -pPASS -D DATABASE < /path/to/myfile.sql HTH, Dan On 8/14/07, Beauford <[EMAIL PROTECTED]> wrote: > > > > I have been trying for days to find a simple command in > > MySQL where I > > > can automatically delete records based on some criteria after a > > > certain timeframe. > > > > > > I found this in the MySQL manual, but I guess it only works with > > > version 5.1+. Unfortunately the version I need this to work > > on is 4.1, > > > and can't be upgraded. > > > > > > CREATE EVENT e_hourly > > > ON SCHEDULE > > > EVERY 1 HOUR > > > COMMENT 'Clears out sessions table each hour.' > > > DO > > > DELETE FROM something; > > > > > > Is there something similar in version 4.1 that will do the > > same thing. > > > > No. But there are cron jobs :-) And if you're deleting many > > rows and you don't want to interfere with other running > > queries (sounds like this is an OLTP system), try MySQL > > Archiver with the --purge argument > > (http://mysqltoolkit.sourceforge.net/). It's much more > > efficient at large data volumes than a single DELETE statement. > > > > Baron > > > > Really. I thought it would have some kind of scripting capability. I did > check out the link, but really don't need anything that extensive. > > Is there a way to run the following command via cron. > > delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) >= 30 and > status != "Y"; > > Thanks > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
