CODEWITHSUNDEEP
mysqlsql
Rayleigh
Rayleigh

Reputation: 37

Issue with the syntax of SQL event

I'm trying to run this code on MySQL:

SET GLOBAL event_scheduler = ON;
CREATE EVENT timer
ON SCHEDULE EVERY 1 MINUTE
DO // this is the line of error
IF CURRENT_TIMESTAMP > timer THEN UPDATE users SET online = '0' WHERE CURRENT_TIMESTAMP > timer;
END IF;

But it returns: #1064 - Syntax error near '' on line 4. Where is the error?

Upvotes: 0

Views: 20

Answers (1)

WEBjuju
WEBjuju

Reputation: 6581

As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords, as shown in this example from that page

delimiter |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

delimiter ;

An idea of how your SQL could work with this idea will be:

SET GLOBAL event_scheduler = ON;

-- change the delimiter so you can embed ; in your event_body
delimiter |

CREATE EVENT timer
    ON SCHEDULE
      EVERY 1 MINUTE
    DO
      BEGIN
          IF CURRENT_TIMESTAMP > timer
            THEN UPDATE users SET online = '0' WHERE CURRENT_TIMESTAMP > timer;
          END IF 
      END |

-- reset the delimiter
delimiter ;

Upvotes: 1

Related Questions