A simple stored procedure run by the event scheduler at predetermined times will do this quite easily. Can you post the output of SHOW CREATE TABLE for your two tables?
John On 23 May 2011 13:15, Machiel Richards <machi...@rdc.co.za> wrote: > Hi Everyone > > I have posted this question quite a while back and noticed now > that I haven't gotten an answer as yet and this is still on my > unresolved list. > > We have 2 tables which we want to archive data from. > > We need to be able to delete all data from the 2 tables that is > older than the start of yesterday. > > The process of determining the rows manually is easy enough, > however we would like to cut out manual intervention and automate this > process. > > I have played around with trying to create a stored routine / > function which is then passed the date as a parameter and then to use > this to determine the rows and delete them. > > - This created 2 problems for me though > > -- initially I struggled with the date format > because it does not want to accept the date format. > -- when at last it seemed to accept the date > format , it caused the script to not exit upon completion. > > I am sure there must be a better way to do this than using > functions and linux scripts to automate the process, however my > experience is failing me in this respect as I am fairly new to the dba > scene. > > > I would really appreciate it if someone can assist me in this > regard please..... > > Regards > Machiel > > PS.... someone already suggested me using MySQL scheduled events , > however I was not able to figure out how to use it for this > functionality though. > > > > > -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk