SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    scheduled update queries

    I searched on this and didn't run into anything so if someone could point me in the right direction I would appreciate it.

    I want to run an update every night just after midnight (server time) to update the status of records in a table that are expired based on an expired date carried in the table. I can write the syntax of the query just fine but I don't know how to make it fire on a schedule.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bostboy View Post
    ... to update the status of records in a table that are expired based on an expired date carried in the table.
    maybe it's me, but this seems like overkill

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe so. I have 4 types of active statuses and two types of expired statuses, so it's a little more than just the expired date. In other words, I'm not just setting the status to active if it's before the date and expired if it's after the date. It will be set to one if there is certain activity and another if there is not.

    I suppose I could do that logic every time I use this info but it seems a lot easier to do it once and then when I am processing the data later it is much easier. For example, when I retrieve the data, I want to sort it by the different statuses. If I have to do that logic, it seems much more complex to me, if it is even possible.

    Bad idea?

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    For running scripts on a schedule in Linux you have cron jobs and in windows you have task scheduler
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the input.

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I haven't used them yet but mysql events introduced in mysql 5.1 should be just want you are looking for.

  7. #7
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks like what I am looking for. I will read up on it and give it a try. They do mention that it is similar to cron jobs and task scheduler which was previously mentioned. Does anyone have any practical experience as to which is better assuming one is.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •