Mysql IF THEN SET

Question

I have a table with

*PwdSettings that has a value of -50 (which is say 50 days)

*PwdDate (which is the date last updated)

*ExpDate (which is 0 or 1, if Password is old i need it set to 1)

Im trying to write a query to say "IF PwdDate is < PwdSettings Then SET ExpDate = 1"

Here is what I have tried so far, everything throws an error.

UPDATE wp_users SET ExpDate = 1 WHEN PwdDate < DATE_ADD(CURDATE(), INTERVAL -50 DAY) WHERE user_email='[email protected]'

UPDATE wp_users SET ExpDate = 1 WHERE user_email='[email protected]' IF(PwdDate < DATE_ADD(CURDATE(), INTERVAL -50 DAY))

IF PwdDate < DATE_ADD(CURDATE(), INTERVAL -50 DAY) WHERE user_email='[email protected]' THEN SET ExpDate = 1

Answer 1

Put the extra condition in the WHERE clause:

UPDATE wp_users
SET ExpDate = 1
WHERE user_email='[email protected]'
AND PwdDate < CURDATE() - INTERVAL 50 DAY

Answer 2

Use:

UPDATE wp_users 
   SET ExpDate = 1 
 WHERE PwdDate < DATE_ADD(CURDATE(), INTERVAL -50 DAY) 

...or, using DATE_SUB:

UPDATE wp_users 
   SET ExpDate = 1 
 WHERE PwdDate < DATE_SUB(CURDATE(), INTERVAL 50 DAY) 

Answer 3

Here is how i did it.

UPDATE wp_users 
SET ExpDate = IF(PwdDate <= DATE_ADD(CURDATE(), INTERVAL PwdSettings DAY), 1, 0)
WHERE user_email='[email protected]'