Deleting all unactivated accounts

I have a question about deleting all unactivated accounts that has been registered over 24 hours. I am using MSSQL.

Database Setup:

account | usrActivated | regT开发者_开发知识库ime
test           0         1314223187

SQL Query:

$time = 24 * (60 * 60);
DROP FROM ACCOUNT_TBL_DETAIL WHERE usrActivated=0 AND regTime < $time

Would this work if I execute a query like this? Deleting unactivated account which are older than 24 hours.

delete from ACCOUNT_TBL_DETAIL
where usrActivated=0 AND regTime <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY));

assuming you're on MySQL.

drop is for removing tables from a database. delete is for removing records from a table.

Not like you've expressed, since that won't delete anything at all. Unless you have accounts created in 1970, anyway. Try this:

DELETE FROM ACCOUNT_TBL_DETAIL
WHERE usrActivated = 0
  AND regTime < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)

Don't forget to do a test run (replace DELETE with SELECT *) to make sure this does what you want.

Not to sound too skeptic, but I guess you might end up deleting someone registered a second before the actual execution of this SQL. He/She might be in the middle of Activating.

Let us say I register at 11:59 PM, browse through channels to watch something better. The delete script kicks in at 12:00 AM (1 min after I start to register). I finally decided to watch Late Night with Jimmy Fallon. I am back to complete the registration - attempt to activate. What would happen then? – Arun Aug 24 '11 at 15:50

You could make it to where it deletes the inactivated individually. not just one day after they register but maybe 3 days? or however long you want to. But it isn't like the script would fire off every three days for the whole site. It would be just one person. Unless multiple users sign up at the same time then that would be the only time that script would fire off. Hope that answers your question.