-1

How to create a MySQL statement to check expiry and and deactivate a/c on matched date?

desc mailbox;
username varchar
pass     varchar
created  datetime
expiry   datetime
active   tinyint(1)

I need to expire a/c (username filed) on expiry date which should be active=0

2
  • This question is off-topic and would be better asked on StackOverflow (where you already have an account).
    – tombull89
    Jan 24, 2013 at 8:52
  • Perhaps on dba.se Jan 24, 2013 at 9:15

1 Answer 1

1

Did you think yourself about the problem before you asked here? The statement is pretty easy to do, just translate english to SQL:

Update my mailbox table and set active to 0 where expiry is less than now:

UPDATE mailbox SET active = 0 WHERE expiry < NOW()

Notice: It would be better to check expiry < NOW() every time you select something from the table because an entry could expire and be queried before the next run of your expiry-statement.

1
  • An even cleaner approach might be to use a view containing the necessary constraints (filtering for "active" and "expiry").
    – the-wabbit
    Jan 24, 2013 at 10:11

Not the answer you're looking for? Browse other questions tagged .