0

I insert my data using INSERT INTO SELECT then I have ON DUPLICATE KEY UPDATE like this:

  ON DUPLICATE KEY UPDATE  
  first_name = COALESCE(eb.first_name, employee.first_name), 
  middle_name = COALESCE(eb.middle_name, employee.middle_name), 
  last_name=COALESCE(eb.last_name, employee.last_name), 
  nickname=COALESCE(eb.nickname, employee.nickname), 
  gender=COALESCE(eb.gender, employee.gender), 
  address=COALESCE(eb.address, employee.address), 
  mobile=COALESCE(eb.mobile, employee.mobile),
  birthdate=COALESCE(eb.birthdate, employee.birthdate), 
  status=COALESCE(eb.status, employee.status), 
  tr_email=COALESCE(eb.tr_email, employee.tr_email),
  position_id = COALESCE(position.id, employee.tr_email);

Now on the same table, there's a column modified_date, that I need to also update the value(insert the current date) when any column above gets updated. Is there a solution without creating a new table or some sort to compare the old values to the new one? Many thanks in advance.

Akina
  • 39,301
  • 5
  • 14
  • 25
quielfala
  • 361
  • 4
  • 18
  • Couple of points. If you are in the `ON DUPLICATE KEY UPDATE` section, you can say that you are doing an update and not an insert, so you could just add the mod to `modified_date` as part these other column updates – RiggsFolly Apr 14 '22 at 13:26
  • 1
    DATETIME and TIMESTAMP columns may have an option ON UPDATE CURRENT_TIMESTAMP. It will be updated automatically, without any additional code. – Akina Apr 14 '22 at 13:26
  • Or there is a way of creating a column that will automatically be updated with the current date when an update happens https://stackoverflow.com/a/28876096/2310830 – RiggsFolly Apr 14 '22 at 13:29
  • Thanks for the comments. I guess I'm okay now with @Akina's solution. I used ON UPDATE CURRENT_TIMESTAMP. – quielfala Apr 14 '22 at 14:38

0 Answers0