I want to use mysql event to update a column in events table with case statement in Mysql Workbench-CodePudding

CREATE EVENT update_status
  ON SCHEDULE EVERY 2 SECOND
  DO
    UPDATE `practiceme.events` as e 
    set `e.status` = 
    CASE
        WHEN CURRENT_DATE < `e.eventStart` THEN 'Upcoming'
        WHEN CURRENT_DATE > `e.eventStart` THEN 'Ongoing'
        WHEN CURRENT_DATE > `e.eventEnd` THEN 'Past'
        ELSE `status`
    END

As of now I have this which is not working as my status column in events table is not updating. This is what I have now when I run SHOW EVENTS FROM practiceme; This is my events table

eventStart is the startofdate of this event, eventEnd is the endofdate of this event.

I want to update the status column in events table when the current date is smaller than eventStart then set column status as Upcoming. If current date is more than eventStart then set column as Ongoing. If current date is more than eventEnd then set column as Past. Also check for all rows.

CodePudding user response：

The backticks are causing the table name to be read as "practiceme.events" instead of what you intend (the events table in the practiceme schema). Your error log will probably indicate that the table practiceme.practiceme.events does not exist. Same for the column names.

Try this:

CREATE EVENT update_status
ON SCHEDULE EVERY 2 SECOND
DO
UPDATE `practiceme`.`events` as e
set `e`.`status` = 
CASE
WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past'
WHEN CURRENT_DATE < `e`.`eventStart` THEN 'Upcoming'
WHEN CURRENT_DATE > `e`.`eventStart` AND CURRENT_DATE < `e`.`eventEnd` THEN 'Ongoing'
ELSE `status`
END