mysql automation

Question

I have a table called "teams" with column "inactive" and another table "events" with column "time". How can I have the "inactive" column updated (to true), if the latest date in "events" for any team occurred X amount of time ago?

I know this can be doe with a php script, but i'm looking for a sql solution

Answer 1

In MySQL 5.1 you have EVENTs ...basically this is like a cron job, but inside the database:

http://dev.mysql.com/doc/refman/5.1/en/events.html

You can set one up and let it run, well, as often as you need to do the update. something like:

delimiter //

CREATE EVENT update_inactive_teams
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE
ENABLE
DO 
UPDATE teams
SET    inactive = 1
WHERE  now() - 14 DAY > (
       SELECT MAX(event_date)
       FROM   events  e
       WHERE  e.team_id = teams.id
       )
//
delimiter ;

You may need t configure the server to enable events: http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

SET GLOBAL event_scheduler = ON;

Unfortunately, you need the SUPER privilege to enable it: http://dev.mysql.com/doc/refman/5.1/en/events-privileges.html

Answer 2

update teams t
   set t.inactive = 1
 where :x > (select max(e.time) 
               from events e 
              where e.team_id = t.id) - current_time;

yes, you can put this into an Event or run with mysql from a cron script.