mysql - How to update table after a certain time interval

Question

How can I update a table after some time interval when a condtion is matched?

tb_contest
id   contest_id   name   is_expire
1    101          new    0
2    102          old    0

tb_answer
contest_id   answer_id   date
101          1           2012-02-02
101          2           2012-09-14
102          5           2012-06-01

I need to update tb_contest after some condition was met and make is_expire=1 after 2 days on basis of the last answer received i:e 2012-03-14, so the tb_contest should be updated on 2012-09-16.

Answer 1

You could use MySQL's event scheduler:

CREATE EVENT expire_contests
  ON SCHEDULE EVERY DAY
     STARTS CURRENT_DATE
  DO UPDATE tb_contest JOIN (
       SELECT   contest_id, MAX(date) AS latest
       FROM     tb_answer
       GROUP BY contest_id
     ) t USING (contest_id)
     SET    tb_contest.is_expire = 1
     WHERE  tb_contest.is_expire <> 1
        AND t.latest <= CURRENT_DATE - INTERVAL 2 DAY