Update Column to CURRENT_TIMESTAMP when Row is Updated in MySQL
To update column value to CURRENT_TIMESTAMP whenever the row is updated in MySQL Table, use ALTER TABLE CHANGE COLUMN query with ON UPDATE.
In this tutorial, we will learn how to update timestamp to current timestamp when an update is made to this row.
Syntax – Update value to Current Timestamp
The syntax to update timestamp column whenever row is updated is provided below:
ALTER TABLE table_name CHANGE column_name column_name TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE table_nameupdates table schema.
CHANGE column_nameupdates the column to.
column_name TIMESTAMP NOT NULLdefines the column as of datatype TIMESTAMP.
DEFAULT CURRENT_TIMESTAMPsets the default value of the column to CURRENT_TIMESTAMP.
ON UPDATE CURRENT_TIMESTAMPmakes the column value to CURRENT_TIMESTAMP whenever there is an update to the row.
Example 1 – Update to Current Timestamp
Consider the table,
useractions, with the following data.
Now we shall update
action to sleeping where
If you observe, the
action is updated as expected, but the
updateon column is not updated because we have not defined the column to update when the row gets updated.
We shall run the following query to update the definition of
updateon column to have CURRENT_TIMESTAMP whenever the row is updated.
ALTER TABLE useractions CHANGE updateon updateon TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Now we shall try to make an update to the row where userid=2.
We have learned to modify a column to update to CURRENT_TIMESTAMP whenever the row is updated.