1
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.

jack
  • 87
  • 6
  • 'when the eventStart is smaller than current date then set column as Upcoming' but you WHEN CURRENT_DATE < `e.eventStart` THEN 'Upcoming' – P.Salmon Dec 23 '22 at 16:14
  • thank you for replying, I have edited the way I wrote. – jack Dec 23 '22 at 16:20

1 Answers1

0

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
jack
  • 87
  • 6
Valerie Parham-Thompson
  • 1,516
  • 1
  • 11
  • 21
  • Thank you so much, It is working now however it changed my status to Ongoing when it should show Past as the eventEnd I input is 6 Dec, do you know why is my WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past' not working? – jack Dec 23 '22 at 16:54
  • 1
    I have solved it by moving WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past' to the top. Thank you so much for helping me. – jack Dec 23 '22 at 17:09
  • The CASE statement will end when it evaluates to true. In the current statement, it is stopping at "Ongoing" and so doesn't get to the "Past" value. If I'm correct about your intended logic, you could make the "Ongoing" line something like: WHEN CURRENT_DATE > `e`.`eventStart` AND CURRENT_DATE < `e`.`eventEnd` THEN 'Ongoing' – Valerie Parham-Thompson Dec 23 '22 at 17:09
  • 1
    Yup, you are right. This works perfectly! I will edit your answer with the updated one! – jack Dec 23 '22 at 17:18