Posted by --CELKO-- on 03/27/07 01:55

>> Consider the following table <<

Where is it? Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it. Here is my guess:

CREATE TABLE Customers
(cust_id CHAR(10) NOT NULL PRIMARY KEY, --wild guess
acctexpiry_date DATETIME NOT NULL,
..);

Notice I dropped the redundant BIT column. Some newbie actually used
a proprietary, low-level BIT data type. You need to fix that at once
and teach the guy that SQL has no BOOLEAN data types -- that is just
sooooo fundamental!

>> Now, I want to update the account_status to FALSE as soon AS the current date becomes accountexpiry_date. <<

Just like you would do this in a punch card system 50 years ago!
Running updates to physical storage every day? You are missing the
fundamental concepts of RDBMS in this design. Each row of a table is
a fact that should stand by itself. Use a VIEW not an assembly
language bit flag!!

CREATE VIEW ActiveCustomers (..)
AS
SELECT cust_id, acctexpiry_date, ..
FROM Customers
WHERE CURRENT_TIMESTAMP < acctexpiry_date;

And then you need to consider how much history and account status
codes you want. Do you need to design an acct_status code? Etc.

[Back to original message]