Skip to main content
Known Participant
March 8, 2011
Answered

Updating a variable based on checking expiration date against now() in each row of a table

  • March 8, 2011
  • 1 reply
  • 419 views

I’m using Coldfusion 9,0,0,251028 on Windows 7 64-bit, with a Microsoft Access 97 database.

I’m trying to create a query which loops through all the rows in a table and checks if the current date is later or earlier than the expiration date. 

If the expiration date column is earlier than now(), it sets the column “is_current” to 0, which is a variable that controls whether or not a message displays on a different page(breakingnews.cfm). 

The column that has the expiration date is “exp_dat” in the table “news”. The query I have so far is:

<cfquery name="expire" datasource="#db#">

update news

set is_current = 0

where exp_dat < now()

</cfquery>

What this ends up doing is filtering out the expired items correctly, but once a new row is inserted, the previous items(which haven’t expired yet)

are still having the “is_current” column set to 0.

The query is cfincluded on the page the messages are supposed to display on (breakingnews.cfm). 

How can I get the query to loop over each row every time to check if the now() is earlier or later than “exp_dat” without setting all the other row’s “is_current” to 0?

    This topic has been closed for replies.
    Correct answer Owainnorth

    Two things:

    1 - A query will never affect rows you haven't told it to. Therefore if new rows have the is_current set to 0, then it's because you're either setting it so in your insert statement or you've set a default value on the column. Just make sure you set the column to 1 when you insert the new row.

    2 - Why are you doing this at all? Say you run your update query (which is relatively intensive) a millisecond before one expires, it'll still be shown on your page. Why are you not just doing "SELECT * FROM mytable WHERE expiration_date > now()"?

    Obviously use cfqueryparams, but that's just an example. It seems at the moment all you're doing is storing out-of-date data in a database and causing yourself a lot more work and overhead.

    1 reply

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    March 9, 2011

    Two things:

    1 - A query will never affect rows you haven't told it to. Therefore if new rows have the is_current set to 0, then it's because you're either setting it so in your insert statement or you've set a default value on the column. Just make sure you set the column to 1 when you insert the new row.

    2 - Why are you doing this at all? Say you run your update query (which is relatively intensive) a millisecond before one expires, it'll still be shown on your page. Why are you not just doing "SELECT * FROM mytable WHERE expiration_date > now()"?

    Obviously use cfqueryparams, but that's just an example. It seems at the moment all you're doing is storing out-of-date data in a database and causing yourself a lot more work and overhead.