CODEWITHSUNDEEP
mysqlsql
Moez
Moez

Reputation: 45

Add value to a column each month in Mysql Data Base

is there a way to update automatically the values of a column in a table in Mysql DB with a predifined value and the update should occur each month.

The situation is that i want to update the column balance in the table employees adding 2.5 days each month to each employee and the total amount of days should be reset each two years for each employee.

Upvotes: 2

Views: 1782

Answers (2)

Drew
Drew

Reputation: 24949

Consider using mysql's Create Event strategy that can take away from needing to do cron jobs.

DELIMITER $$
CREATE EVENT monthlyAddFlexDaysEvent
ON SCHEDULE EVERY '1' MONTH
STARTS '2015-09-01 00:00:00'
DO 
BEGIN
 update empAccrued set daysAccrued=daysAccrued+2.5;
END$$
DELIMITER ;

DELIMITER $$
CREATE EVENT annualThingEvent
ON SCHEDULE EVERY '1' YEAR
STARTS '2016-01-01 00:00:00'
DO 
BEGIN
 -- perform some annual thing
END$$
DELIMITER ;

In just about any event you could do something that doesn't fall on say the first of the year. Such as a weekly event that handles updates based on employee anniversary dates.

From the Manual page on CREATE EVENT, 

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

Properly setting up events to even happen and monitoring them is important. 

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

ooops, the event scheduler is not turned on.

Well I can wait all day long, events aren't even turned on

SET GLOBAL event_scheduler = ON;  -- turn her on

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

List all events by schema name:

show events from so_gibberish;

or

show events\G; -- <--------- I like this one from mysql> prompt
show events; -- <--------- from workbench / sqlyog


*************************** 1. row ***************************
                  Db: so_gibberish
                Name: set_trips_finished
             Definer: GuySmiley@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2015-08-23 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

Upvotes: 2

dbers
dbers

Reputation: 654

Databases don't really work that way. They are meant for storing information. You can write triggers so if you update a row or insert then an event happens but other then that the data doesn't change

Something like this should be handled in the application logic. Creat a cron or other periodic job that will grab rows created on X date and update them

Upvotes: 3

Related Questions