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

[BarrettChamberlain]

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?

[Owainnorth · Accepted solution]

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.