How to update data in MySQL periodically?

Question

Possible Duplicate:
Is there an equivalent to cron in Windows?

How do I update my data in MySQL periodically?

Say I have table. One column is "Date". Another column is "Expiry_Status".

How do I update the table periodically(a few hours) such that, if the date in the "Date" column is after the current date, the "Expiry_Status" will be updated to "Yes" value.

I am using WAMP as the framework.

Edit: Thanks for the answers but a lot of you guys gave answers about changing the database architecture. The case I gave above is just a simple example. I am more interested on how databases are updated regularly. ie: the program used, the syntax, the paradigms....etc etc. Thanks again.

I think this must be done with a programming language. Since you are on Windows, you should consider using Task Scheduler to run a script that will update your database however so often you like. — user1477388, Jan 15 '13 at 17:37
You could set up a cron-job / task scheduler job, but why would you want to do that? Either one is sufficient, storing both the date and the status is storing duplicate information and can easily lead to inconsistencies in your database. — jeroen, Jan 15 '13 at 17:37

score 1 · Answer 1 · answered Jan 15 '13 at 17:38

You write a program to perform the update, and then run it using a schedule system.

On Windows (since you tagged the question WAMP), this is usually done with Task Scheduler.

Other operating systems usually use cron.

Changing a status column is redundant though. You can search for data where the expiry time is greater than NOW() instead of where the status is Yes. This sort of scheduled script is normally used when the old data is being deleted.

score 1 · Answer 2 · edited May 23 '17 at 12:30

1

write a php script which checks times and updates appropriately then use windows task scheduler to run them at certain interval. (assuming windows os...)

edited May 23 '17 at 12:30

Community

1
1

answered Jan 15 '13 at 17:38

Pedro del Sol

2,840
9
39
52

score 1 · Answer 3 · answered Jan 15 '13 at 17:43

What value does having this expiry_status even serve? If you have an index in that date/datetime field (which you would need to effectively update the expiry_status field anyway), you can just as easily query on...

WHERE `date` > NOW()

...in order to determine active records. That is in essence all you are going to be doing with this proposed update query anyway. Why maintain an extra field if you don't need to?

How to update data in MySQL periodically?

3 Answers3