Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

closed as off topic by Michael Hampton, Tom O'Connor, Khaled, Alex, Lucas Kauffman Jan 24 '13 at 13:34

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This question is off-topic and would be better asked on StackOverflow (where you already have an account). –  tombull89 Jan 24 '13 at 8:52
    
Perhaps on dba.se –  Tom O'Connor Jan 24 '13 at 9:15

1 Answer 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.

share|improve this answer
    
An even cleaner approach might be to use a view containing the necessary constraints (filtering for "active" and "expiry"). –  the-wabbit Jan 24 '13 at 10:11
    
thanks Dehalion : –  rajiv Jan 24 '13 at 14:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.