Update Status Field after Expiry Date

[shane.taylor81]

Consider the following table

Customer
custId char(10)
accountExpiryDa te datetime
accountStatus bit

Now, I want to update the accountStatus to False as soon as the
current date becomes accountExpiryDa te.

I think it can be done using "SQL Agent" but my webhost doesnt provide
me access to that. I have access only to the Query Analyzer.

Thanks
Shane

[Ed Murphy]

Hugo Kornelis wrote:

On Thu, 29 Mar 2007 18:08:00 -0700, Ed Murphy wrote:

>--CELKO-- wrote:

>>All data types have to be NULL-able in SQL. Having a BOOLEAN type
would lead to 4 valued logic with inconsistent rules about how NULLs
propagate. And the various vendor extension do not work or port
either.

It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything?

Hi Ed,

You've overlooked the basic rule of NULL propagation: any expression
involving NULL results in NULL. In the tables above, there are
exceptions to this rule, such as NULL AND FALSE resulting in FALSE, and
TRUE OR NULL resulting in TRUE.

That rule is oversimplified. Really, it should be "any expression
whose value _depends_ on a NULL input results in NULL", i.e. could
replacing the NULL with different non-NULL values lead to different
values of the expression? NULL + 2 qualifies; NULL = 'ABC' qualifies;
but NULL AND FALSE does not, and neither does TRUE OR NULL.

(As usual, IS NULL and IS NOT NULL remain special cases.)

[--CELKO--]

>It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything? <<

The NULL propagation rule.

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T N T T | F
N | N N N N | N N N N | N
F | F N F F | T N F F | T

This means that TRUE OR NULL = NULL, etc. and you can now prove that
TRUE = FALSE. The UNKNOWN logical value does not have this behavior
and that is why we have it.

[Ed Murphy]

--CELKO-- wrote:

>>It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything? <<

The NULL propagation rule.

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T N T T | F
N | N N N N | N N N N | N
F | F N F F | T N F F | T

This means that TRUE OR NULL = NULL, etc. and you can now prove that
TRUE = FALSE. The UNKNOWN logical value does not have this behavior
and that is why we have it.

NULL represents the concept "unknown" in all other contexts; it should
represent it in the context of the Boolean data type as well.

IINM, while SQL doesn't have a mandatory Boolean *type*, it already
follows TRUE OR NULL = TRUE and FALSE AND NULL = FALSE in Boolean
*expressions*. Example:

CREATE TABLE Table1 (Column1 varchar(10), Column2 varchar(10))

INSERT INTO Table1 (Column1, Column2) values ('A' , null)
INSERT INTO Table1 (Column1, Column2) values (null, 'B' )

SELECT * FROM Table1 WHERE Column1 = 'A' OR Column2 = 'B'
-- returns 2 rows

SELECT * FROM Table1 WHERE Column1 = 'A' AND Column2 = 'B'
-- returns 0 rows