How to Update to CURRENT_TIMESTAMP in MySQL?

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;

where

• ALTER TABLE table_name updates table schema.
• CHANGE column_name updates the column to.
• column_name TIMESTAMP NOT NULL defines the column as of datatype TIMESTAMP.
• DEFAULT CURRENT_TIMESTAMP sets the default value of the column to CURRENT_TIMESTAMP.
• ON UPDATE CURRENT_TIMESTAMP makes 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 userid=2.

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.