How to run two update query in mysql event scheduler?

Question

Mysql event scheduler is like this :

    CREATE EVENT update_status
    ON SCHEDULE EVERY 2 MINUTE
    DO
    BEGIN
    UPDATE customer_group
    SET is_status = 0
    WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date;
    UPDATE customer_group 
    SET is_status = 1
    WHERE CURRENT_TIMESTAMP NOT BETWEEN start_date AND end_date;
    END

When I run the script, there is exist error like this :

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

Any solution to solve my problem?

Thank you very much

That is why I wrapped it in a delimiter in your previous question. — Drew, Jun 15 '16 at 03:24

meda · Accepted Answer · 2016-06-15T04:22:22.737

3

Try this :

DELIMITER $$
CREATE EVENT update_status
ON SCHEDULE
    EVERY 2 MINUTE
DO
BEGIN
    UPDATE
       customer_group      
    SET
       is_status = 0      
    WHERE
       CURRENT_TIMESTAMP BETWEEN start_date AND end_date;      
    UPDATE
        customer_group       
    SET
       is_status = 1      
    WHERE
        CURRENT_TIMESTAMP NOT BETWEEN start_date AND end_date;
END $$
DELIMITER ;

edited Jun 15 '16 at 04:22

answered Jun 15 '16 at 02:26

meda

45,103
14
92
122

Thank you very much – moses toh Jun 15 '16 at 03:28
I need you help. Look here : http://stackoverflow.com/questions/38175735/how-to-get-data-in-function-extend-controller – moses toh Jul 04 '16 at 02:19

score 0 · Answer 2 · answered Jun 15 '16 at 02:40

You can do your above operations( 2 updates) in single update itself

use the below script

CREATE EVENT update_status
ON SCHEDULE EVERY 2 MINUTE
DO
UPDATE customer_group
SET is_status = (select case when CURRENT_TIMESTAMP BETWEEN start_date AND end_date then 0 else 1 end from customer_group limit 1);

for more details use this http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

How to run two update query in mysql event scheduler?

2 Answers2

Linked