在一定时间间隔后如何更新表 [英] How to update table after a certain time interval

问题描述

匹配条件后，如何在一段时间后更新表?

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

我需要在满足某些条件后更新tb_contest，并根据收到的最新答案(即2012-03-14)在2天后设置is_expire=1，因此应在2012-09-16上更新tb_contest.

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.

推荐答案

尝试一下，

UPDATE tb_contest a INNER JOIN
        (
            SELECT contest_ID, MAX(`date`) maxDate
            FROM tb_answer
            GROUP BY contest_ID
        ) b ON a.contest_ID = b.contest_ID
SET a.is_expire = 1
WHERE   DATEDIFF(CURDATE(), b.maxDate) >= 2 AND 
        a.is_expire = 0

因此，这两个表由contest_ID连接在一起，并且在tb_answer上具有最新的回答日期.通过使用DATEDIFF()，我们可以知道今天的日期和比赛被回答的日期之间的差额.

So here it goes, the two tables were joined by contest_ID and having the lastest answered date on tb_answer. By using DATEDIFF() we can know the difference between today's date and the date the contest has been answered.

这篇关于在一定时间间隔后如何更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！