How to add "ON update current timestamp" to existing table column

Question

I have a column, say ts_activity, with datatype timestamp in MYSQL and default value to current timestamp. I want to add on update apply current time stamp to this column value.

I am not able to get alter query to do this. While create new table and its column I can add that, but not able to modify existing column by adding on update apply current timestamp.

Can someone tell me exact query to handle this? Can we alter this after creating table?

Answer 1

Devart's suggestion will work, but I prefer to use MODIFY COLUMN instead of CHANGE COLUMN if I'm not renaming the column.

I also assume that your ts_activity column is not nullable since you have a default, so I am setting it to NOT NULL, but that's up to you.

This is the statement I would use:

ALTER TABLE your_table
  MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;