Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Easy question for MySQL experts (i think)

Guest
Jan 26, 2013 Jan 26, 2013

Hi all. I have a question that should be super easy for mySQL experts....

let's say i have a table that has a field with a timestamp value (let's call it lastmodified). let's say i want to have my php script run a mysql query that does basically this (in pseudo code, i know it's not the right syntax) :

UPDATE table mytable SET status = "archived" WHERE lastmodified >= (60 days ago) AND status="complete"

in other words i want it to comb the records and find any record that has been complete and unmodified for at least 60 days, and set its status field to "completed"

TOPICS
Server side applications
614
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 27, 2013 Jan 27, 2013

UPDATE table mytable SET status = "archived" WHERE lastmodified <= DATE_SUB(CURRDATE(), INTERVAL 60 DAY)  AND status="complete"

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 27, 2013 Jan 27, 2013
LATEST

bregent, thanks for the input, ironically i had just figured it out myself, but i went about it a slightly different way. Since i found out that one could easily set the database to have "on update current_timestamp" to automatically put a timestamp every time the record was updated, it made sense to continue using timestamps not "currdate". so the solution i used ended up being :

UPDATE mytable SET status='archived'

WHERE status='complete' AND lastmodified < TIMESTAMPADD(DAY,-60,NOW())

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines