apaul apaul - 26 days ago 7
MySQL Question

Automatically Changing Each Value of a MySQl column

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';

Answer Source

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;