Automatically Changing Each Value of a MySQl column

Question

I have a column with numerous integer values. How can I code it so that every twenty-four hours each value is subtracted by one until it reaches 0? Till now, I have understood the code for repeating an event every 24 hours but could not find a way to subtract one from each individual value. Thank you!

SET GLOBAL event_scheduler = ON;
SELECT @@event_scheduler; 
CREATE EVENT e_store_ts 
ON SCHEDULE
  EVERY 24 HOURS  
DO
  UPDATE users set value= '1';

score 0 · Accepted Answer · answered Sep 25 '17 at 04:23

You can use case statement to update only values which are greater than zero

SET GLOBAL event_scheduler = ON;
SELECT @@event_scheduler; 
CREATE EVENT e_store_ts 
ON SCHEDULE
  EVERY 24 HOURS   
DO
  UPDATE users SET `value`= CASE WHEN `value` > 0 THEN `value` - 1 ELSE `value` END;

Automatically Changing Each Value of a MySQl column

1 Answers1