Skip to main content
A
Anonymous
January 27, 2013
Question

Easy question for MySQL experts (i think)

  • January 27, 2013
  • 1 reply
  • 609 views

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"

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
January 27, 2013

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

A
Anonymous
January 27, 2013

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())

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices