Reset to default value depending with the year-CodePudding

I want to automatically reset the value in hours_remaining to its default value according to year. I also want the year to auto-incremented. Can anyone give me some advise or help on how to implement this? By the way I'm new to using Php MySql.

From this:

 employee_id |  year  | default_value | hours_remaining |
 ========================================================
       1     |  2022  |      80       |        67       |  <---- When current year

To this when another year:

 employee_id |  year  | default_value | hours_remaining |
 ========================================================
       1     |  2023  |      80       |        80       |  <---- When year is changed

CodePudding user response：

This is the perfect use of MySQL's EVENT, which can perform periodic jobs. Just make sure the event scheduler is toggled on. Then create a event and watch it happens:

delimiter //
set global event_scheduler=on //
drop event if exists newyear_update //

create event newyear_update on schedule every 1 year starts '2023-01-01' do 
BEGIN
update testtb set year=year 1,hours_remaining=default_value ;
END//

Of course you are not going to wait another 8 months to find out. To test it, you can just change the time to every 60 second starts now()