Clever Geek Handbook

Resetting Column Values ​​After Time

I am new to phpmyadmin and SQL databases.

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

+3


source to share


1 answer


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

This is where some configuration tweak is done to ensure your MySQL server is properly handling this event.



This is the actual update request.

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

Pay attention to the offer WHERE

. This prevents redundant updating of rows that already have a null column value forcounter.

+2


source






More articles:


All Articles