Search code examples
mysqltimermysql-event

resetting column values after time

I am fairly new to phpmyadmin and SQL Databases.

I would like to know how I can add a "script" that resets a value in all columns of a table after a certain time period -> I want the column to set the int value to 0 every 72hours for every row. Is that possible and how?

Solution

  • What you want is called an "event". Here's a potential definition; your needs may vary.

    CREATE EVENT `zero_my_column`
    ON SCHEDULE
        EVERY 72 HOUR STARTS '2015-07-13 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE
DO BEGIN
         UPDATE mytable SET counter = 0 WHERE counter <> 0;
END

    There's some configuration work to do to ensure your MySQL server will run this event correctly.

    This is the actual update query.

    UPDATE mytable SET counter = 0 WHERE counter <> 0;

    Notice the WHERE clause. That prevents redundant updating of rows that already have a zero column value for counter.