Jack Lauman <[EMAIL PROTECTED]> wrote on 02/04/2005 11:57:37 AM:
> I want to run a nightly cron job where you iterate throught each row of
> a single table and reset a field depending on the conditions specified.
> In this case I want to take a date field (RenewalDate) and compare it
> to the current date less a 30 day grace period and if true reset the
> (SubscriptionEpired) field to a 1 (true).
>
> RenewalDate >=(${now} - 30) SubscriptionExpired = 1
>
> Both fields are in a single table called Restaurants
>
> RenewalDate date (yyyy-mm-dd)
> SubscriptionExpired int(1)
>
> Will something like this work or do I need something different?
>
> UPDATE Restaurant SET SubscriptionExpired = 1 WHERE TO_DAYS(NOW) -
> TO_DAYS(RenewalDate) < 30;
>
> Thanks,
>
> Jack
>
>
Yes, that would do what you want but because you have a function on the
left side of an = in your WHERE clause, you eliminate the possibility to
use an index on that field. You might try rewriting your statement this
way:
UPDATE Restaurant
SET SubscriptionExpired = 1
WHERE RenewalDate < (CURDATE() - 30 Days);
It would probably use an index and finish much faster. However, if you
have a timestamp field, that statement will cause your timestamp to reset
for each already expired record, too (because you are resetting all of the
"old" records to 1, even if they are already expired). What you can do to
limit which timestamps are updated is to limit your changes to only those
rows that need changing like this:
UPDATE Restaurant
SET SubscriptionExpired = 1
WHERE RenewalDate < (CURDATE() - 30 Days)
AND SubscriptionExpired = 0;
That may work even faster because it's more selective. "Your mileage may
vary" (YMMV).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
