update or case statement required

Question

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

Check out this question. It should guide on querying multiple columns. https://stackoverflow.com/questions/44706196/mysql-multiple-columns-in-in-clause — gordonturibamwe, Sep 14 '20 at 04:06

score 1 · Answer 1 · answered Sep 14 '20 at 04:14

1

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

answered Sep 14 '20 at 04:14

Joseph Webber

2,010
1
19
38

update or case statement required

1 Answers1