Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :

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

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

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

share|improve this question
    
Have you tried to write an event for this behaviour? Have you written the UPDATE that does this (and which should then go into the event)? –  ypercube Jan 9 at 7:30
    
my query is : update T1 set state=1 where time < date_sub(now(),interval 24 hour) and state=0 or state=2; –  user21546 Jan 9 at 8:18
    
Great. So, put this inside a CREATE EVENT ... statement - and there you go. –  ypercube Jan 9 at 8:20
    
please check and validate in answer box .. –  user21546 Jan 9 at 8:27

1 Answer 1

up vote 3 down vote accepted
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

share|improve this answer
1  
Correct the and state=0 or state=2 to either and (state=0 or state=2) or to: and state IN (0,2) –  ypercube Jan 9 at 8:28
    
ok...any loss of we dont use bracket or IN..or it is just a writing manner ?? –  user21546 Jan 9 at 10:38
2  
Yes, you have to use parenthesis, because AND has higher precedence than OR. –  ypercube Jan 9 at 10:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.