Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column in MySQL?
The ON UPDATE CURRENT_TIMESTAMP defines that an update without an explicit timestamp would result in an update to the current timestamp value.
You can remove ON UPDATE CURRENT_TIMESTAMP from a column using ALTER command.
The syntax is as follows
ALTER TABLE yourTableName CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT NULL default CURRENT_TIMESTAMP;
To understand the above syntax, let us create a table. The query to create a table is as follows
mysql> create table removeOnUpdateCurrentTimeStampDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP - > ); Query OK, 0 rows affected (0.54 sec)
Check the description of the table using DESC command.
The query is as follows
mysql> desc removeOnUpdateCurrentTimeStampDemo;
The following is the output
+---------------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+-------------------+-----------------------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(20) | YES | | NULL | | | UserUpdateTimestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------------------+-------------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.04 sec)
Now look at the Extra field there is on update CURRENT_TIMESTAMP. The query to remove ON UPDATE CURRENT_TIMESTAMP is as follows:
mysql> alter table removeOnUpdateCurrentTimeStampDemo - > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
Check the description of table once again.
The query is as follows
mysql> desc removeOnUpdateCurrentTimeStampDemo;
The following is the output
+---------------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+-------------------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(20) | YES | | NULL | | | UserUpdateTimestamp | timestamp | NO | | CURRENT_TIMESTAMP | | +---------------------+-------------+------+-----+-------------------+----------------+ 3 rows in set (0.00 sec)
If you want to remove the default CURRENT_TIMESTAMP, then the query is as follows
mysql> alter table removeOnUpdateCurrentTimeStampDemo - > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
Check the description of table once again.
The query is as follows
mysql> desc removeOnUpdateCurrentTimeStampDemo;
The following is the output
+---------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(20) | YES | | NULL | | | UserUpdateTimestamp | timestamp | NO | | NULL | | +---------------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
Now look at the above sample output, we have removed the ON UPDATE CURRENT TIMESTAMP.
4K+ Views
