patternsqlMinor

Updating a column after expiration of specific time in MySQL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

afterexpirationcolumnupdatingtimemysqlspecific

Problem

-
I have a table where I have a column named state(int(2)) and modify_time (time_stamp on update current timestamp).

-
I have an update trigger which changes the value of state column to 0 based on some condition.

-
I want to set the value of state column to 1 after 24 hours of modify_time, if it is still 0.

-
I tried below method to test :

CREATE EVENT myevent1 
 ON SCHEDULE AT current_timestamp + interval 1 minute 
 DO UPDATE test.mytabletable SET state = 0;

This did not doing anything. Is there any alternative method?

Solution

SET GLOBAL event_scheduler = ON; -- enable event scheduler.
SELECT @@event_scheduler;  -- check whether event scheduler is ON/OFF
CREATE EVENT e_store_ts  -- create your event
    ON SCHEDULE
      EVERY 24 HOURS  -- run every 24 hours
    DO
      UPDATE myschema.youtable set mycolumn='1'

Code Snippets

SET GLOBAL event_scheduler = ON; -- enable event scheduler.
SELECT @@event_scheduler;  -- check whether event scheduler is ON/OFF
CREATE EVENT e_store_ts  -- create your event
    ON SCHEDULE
      EVERY 24 HOURS  -- run every 24 hours
    DO
      UPDATE myschema.youtable set mycolumn='1'

Context

StackExchange Database Administrators Q#43293, answer score: 2

Revisions (0)

No revisions yet.