Update MySQL column when new date is greater than the existing one-CodePudding

I'm trying to update a column from a MySQL table so that it changes to current timestamp when the new date from another column is greater than the existing date from that column, and stays the same otherwise.

For example, let's say that I have a table with two columns, enddate and enddate_modif. If the date in enddate in the updating record is greater than the date in enddate in the existing table, I want enddate_modif to change to current timestamp. Otherwise, it keeps the same value.

How can I do it in MySQL?

Thank you in advance.

CodePudding user response：

An after update trigger should do the trick.

Try:

CREATE TRIGGER `enddate_modif_trigger` BEFORE UPDATE ON `my_table`
FOR EACH ROW
SET NEW.enddate_modif = (SELECT CASE WHEN NEW.enddate  > enddate_modif
                                  THEN CURRENT_TIMESTAMP  
                                  ELSE NEW.enddate_modif
                                  END
                         FROM my_table 
                         );

CodePudding user response：

I think you can use an after update trigger something like this

if(New.enddate >Old.enddate ) then 
  update yourtable set enddate_modif=NOW() where Your_table_ID=NEW.Your_table_ID;
end iff;