CODEWITHSUNDEEP
mysql
Zia
Zia

Reputation: 59

how to create a jobs scheduler in mysql

I want to create a scheduler jobs in MySql, I have a table like a below

table : count_sla
================================================================================
ID  |   NAME    | STS|  CUT_OFF |   DATE    |   SLA |
================================================================================
1   |   remon   | 1 |   Y   | 2016-05-03    |   1   |
2   |   dedy    | 1 |   N   | 2016-05-03    |   1   |
3   |   sanjay  | 1 |   Y   | 2016-05-03    |   0   |
4   |   arman   | 2 |   Y   | 2016-05-03    |   0   |
5   |   renaldy | 1 |   Y   | 2016-05-03    |   0   |
6   |   sinta   | 0 |   N   | 2016-05-03    |   0   |

I want to change CUT_OFF and SLA column from this table

Upvotes: 0

Views: 1571

Answers (1)

gmiley
gmiley

Reputation: 6604

If you are looking for a query that does the update per your requirements, that would be something like this:

UPDATE count_sla 
   SET 
   CUT_OFF = CASE 
      WHEN CUT_OFF = 'Y' THEN 'N' 
      ELSE 'Y' END, 
   SLA = Now() - DATE;

I don't work in MySQL very frequently, but I think the following should do what you want for scheduling that query:

CREATE EVENT `evnt_update_count_sla` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT 'Updates count_sla daily.'
 DO BEGIN    
    UPDATE count_sla 
       SET 
       CUT_OFF = CASE 
          WHEN CUT_OFF = 'Y' THEN 'N' 
          ELSE 'Y' END, 
       SLA = Now() - DATE;
 END;

Give it a try, if it isn't 100% correct, it should at least get you in the right direction.

Upvotes: 1

Related Questions