Mysql – column auto updated after 24 hours in thesql

eventMySQLmysql-5.5stored-procedurestrigger

i have a table T1 with column :

Name      State             time
mon         1          2014-01-09 11:23:00
tue         0          2014-01-07 14:40:00
wed         2          2014-01-08 09:23:00 
thu         0          2014-01-09 12:23:00

Now i want to schedule an event or develop a trigger (if possible) , if any state is 0/2 and its time is more than 24 hours from its current time then it should be updated to 1.

For example in above table, state of Tue is 0 and time is more than 24 hours , so state should be updated to 1 and same applies to wed , but not for thu.

I went through many questions like :

https://stackoverflow.com/questions/17387802/how-to-auto-update-mysql-after-timestamp-field-expierd

https://stackoverflow.com/questions/12421756/how-to-update-table-after-a-certain-time-interval

but i haven't understand yet , how to solve my problem

Best Answer

CREATE EVENT reset
    ON SCHEDULE
      EVERY 1 HOUR
        DO
update T1 
set state=1 
where time < date_sub(now(),interval 24 hour) 
  and (state=0 or state=2) ;

it will run in every hour

Related Question