I've got a table having a column of type timestamp which defaults current_timestamp and updates to current_timestamp on every update.

I wish to take away the "on update" feature about this column. How do you write the alter statement?

I attempted the next:

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

but this did not work.

thanks in adavance.

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

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

Notice that you need to repeat the column title. Also, make certain you're using backticks rather than single quotes to flee the column title time, which prevents it from being construed because the mysql column kind of time.

By indicating the DEFAULT of CURRENT_TIMESTAMP, MySQL will no more instantly update the column. In the MySQL Manual:

Having a DEFAULT CURRENT_TIMESTAMP clause with no ON UPDATE clause, the column has got the current timestamp because of its default value however is not instantly up-to-date.

You cannot AFAIK use functions for example NOW() like a default.

Try

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

(Edited to include getting away and 2nd utilization of area title)

Try one of these simple:

/* Change datatype to datetime; no DEFAULT, no ON UPDATE */
ALTER TABLE `mytable` CHANGE `time` `time` DATETIME DEFAULT NULL NULL;

/* Don't change datatype; DEFAULT CURRENT_TIMESTAMP, no ON UPDATE */
ALTER TABLE `mytable` CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;