CODEWITHSUNDEEP
mysql
Joe
Joe

Reputation: 1

update or case statement required

I am building a small Web app for the business using MYSQL/PHP and I can't seem to get the right SQL statement. I am very fresh to this so please excuse my ignorance.

I have a date column for insurance documents and I have another column titled Current, where I want to insert/update either a YES or NO into this column based on if the document is older than 2 years.

If i run the following:

UPDATE testdb
SET Current = 'NO' WHERE date < DATE_SUB(NOW(),INTERVAL 2 YEAR)

This works fine but I also need if date > .... to return YES. So if a new document date is entered into the DB in the future for an expired document, it will also update the current column based on the new date. As much as I try to run them together with else, if, and - I always get errors.
Thanks

Upvotes: 0

Views: 35

Answers (1)

Joseph Webber
Joseph Webber

Reputation: 2173

Using https://stackoverflow.com/a/41695199/2305594 as a guide, you should be able to do something like

Update testdb
SET Current =
CASE WHEN date < DATE_SUB(NOW(),INTERVAL 2 YEAR) THEN 
    'No'
ELSE 
    'Yes'
END

Upvotes: 1

Related Questions