62

I have a table with a column of type timestamp which defaults current_timestamp and updates to current_timestamp on every update.

I want to remove the "on update" feature on this column. How do I write the alter statement?

I tried the following: 

ALTER TABLE mytable alter column time  set DEFAULT now();

but this didn't work.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Tihom
  • 3,384
  • 6
  • 36
  • 47

2 Answers2

96

Pete was almost correct but used the wrong syntax for 'change':

ALTER TABLE mytable CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Notice that you must repeat the column name. Also, make sure you are using backticks instead of single quotes to escape the column name time, which prevents it from being interpreted as the mysql column type of time.

By specifying the DEFAULT of CURRENT_TIMESTAMP, MySQL will no longer automatically update the column. From the MySQL Manual:

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

jonstjohn
  • 59,650
  • 8
  • 43
  • 55
12

You can't AFAIK use functions such as NOW() as a default.

Try

ALTER TABLE `mytable` CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

(Edited to add escaping and second use of field name)

Pete
  • 1,773
  • 8
  • 11
  • Got the following: mysql> ALTER TABLE messages CHANGE time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL DEFAULT CURRENT_TIMESTAMP' at line 1 mysql> ALTER TABLE messages CHANGE column time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL DEFAULT CURRENT_TIMESTAMP' at line 1 – Tihom Nov 20 '09 at 14:13
  • Sorry forgot to add escaping, and the field name comes twice. – Pete Nov 20 '09 at 16:25