Mysql – Basics of creating a table and refreshing it via a thesql event

MySQLmysql-event

My apologies in advance for my display of ignorance. Is there a way for the event_body of an event to take the following:

DROP TABLE IF EXISTS compdist1.comp_CL1;

CREATE TABLE compdist1.comp_CL1 AS SELECT * FROM compdist1.prices_CL_analysis;

I am discovering that only one line is being picked up. (the top one). Is there a way to make it do both steps? Basically, drop the table and repopulate it from the view prices_CL_analysis.

Best Answer

Wrap both statements in BEGIN and END, like:

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

Taken from a quick perusal of the MySQL Create Event page.

So, your code might become:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          DROP TABLE IF EXISTS compdist1.comp_CL1;
          CREATE TABLE compdist1.comp_CL1 
          AS 
          SELECT * 
          FROM compdist1.prices_CL_analysis;
      END

Also redefine the MySQL delimiter temporarily before the CREATE EVENT … to a different symbol and use the declared symbol at the end of the definition so that MySQL can pass the entire CREATE EVENT statement to the server. Redefine it back to ; after the definition, like this:

DELIMITER $$

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          …
      END$$

DELIMITER ;

This is needed when passing compound statements. Since the event body contains more than one statement, you event definition now qualifies as a compound statement. More information at the MySQL Defining Stored Programs page.

Related Question