My MySQL Kung Fu is not strong...
June 24, 2014 9:12 AM   Subscribe

I need a script for a MySQL table. The table has multiple fields but one ENDDATE will be populated as a Date/time value. When I am 90 days from that date, I need to update the field STATUS with the value of "email" Work your magic hive! thanks
posted by keep it tight to Computers & Internet (9 answers total)
update table [mytable] set STATUS = "email" where ENDDATE (less than or equal to) TODAY + 90

Will that work? Or do you mean 90 days PAST that date?

(sorry about the code fail for the HTML for less than)
posted by jillithd at 9:17 AM on June 24, 2014

The date could be any day...say it's 11/1/2014...I need the insert 90 days prior to 11/1
posted by keep it tight at 9:20 AM on June 24, 2014

are you planning to run a nightly script to do an update to the field? with a script such as jillithd suggested? or do you need it to do more automatically?
You might look into if you dont have a procedure for running external scripts nightly.
posted by TheAdamist at 9:24 AM on June 24, 2014

OK. Then my script will work as long as you automate it like TheAdamist says. The key is that "TODAY" is a reserved word in SQL that knows what the value of today is. Similar to the today() function in Excel.
posted by jillithd at 9:27 AM on June 24, 2014

So I have UPDATE dwdata.contracts SET PROCESSING_STATUS = "Email" WHERE END_DATE < "TODAY+90"

I am getting the error " incorrect datetime value 'TODAY + 90' for column 'END_DATE' at row 1
posted by keep it tight at 9:45 AM on June 24, 2014

Just poppin in to say it looks like it's reading "TODAY + 90" as a string which it then can't use when comparing to a date.
posted by Brainy at 9:47 AM on June 24, 2014

I don't believe MySQL recognises TODAY, I'm not even sure it is SQL. The other problem is that datetime comparisons can be somewhat tricky as results can vary depending on when you run the query. Best to coerce to date values explicitly using something like:

update tablename set STATUS = "email" where date(date_sub(ENDDATE, INTERVAL 90 day)) <= date(now())
posted by epo at 9:58 AM on June 24, 2014

Thanks epo...that did it!
posted by keep it tight at 10:16 AM on June 24, 2014

Sorry about that. "Today" is definitely recognized by the ESQL queries I run on an Informix database.

I guess I am not familiar enough with the differences between MySQL and Informix SQL.
posted by jillithd at 11:41 AM on June 24, 2014

« Older How have you shared appliance purchases with...   |   Remembrance of Financial Catastrophes Past Newer »
This thread is closed to new comments.