CODEWITHSUNDEEP
mysqlsqldatabasesql-update
user467237
user467237

Reputation: 33

MySQL IF THEN SET

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)

I'm 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

Upvotes: 2

Views: 2287

Answers (3)

user467237
user467237

Reputation: 33

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]'

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

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)

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838696

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

Upvotes: 0

Related Questions