CODEWITHSUNDEEP
mysqltimermysql-event
Brinkhoff
Brinkhoff

Reputation: 43

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?

Upvotes: 4

Views: 2815

Answers (1)

O. Jones
O. Jones

Reputation: 108706

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.

Upvotes: 5

Related Questions