Remove on update condition without drop mysql table

Question

I have a mysql table and data entries in it:

CREATE TABLE    `invoices`.`invoices` (
    `InvoiceID` bigint(20) unsigned NOT NULL auto_increment,
    `UserID` int(10) unsigned NOT NULL,
    `Value` decimal(10,3) NOT NULL,
    `Description` varchar(4048) NOT NULL,
    `DateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY    (`InvoiceID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin5;

I want to remove "on update CURRENT_TIMESTAMP" condition. How can I alter this table?

score 53 · Accepted Answer · answered Sep 06 '10 at 13:28

53

ALTER TABLE `invoices`.`invoices`
    CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

answered Sep 06 '10 at 13:28

aularon

11,042
3
36
41

I only want to remove on update condition. Because I dont want DateTime to be updated on record update. – HasanG Sep 06 '10 at 13:29
I edited it, now it should default to `CURRENT_TIMESTAMP` on new insertions, but stays as is on update. – aularon Sep 06 '10 at 13:33
1

If you want to remove the CURRENT_TIMESTAMP setting when inserting too, use the above query, but replace the CURRENT_TIMESTAMP with "0000-00-00 00:00:00" (including the ") – Pim Sep 07 '15 at 07:43
This worked for me, alter table tmpx change `ts` `ts` TIMESTAMP NOT NULL DEFAULT 0; CREATE TABLE `tmpx` ( `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ; insert into tmpx values(null, null); – almaruf Mar 11 '19 at 10:11

Abhishek Goel · Answer 2 · 2017-01-24T06:18:14.310

7

aularon's ans will not remove the on update condition

It can be removed by using the following 2 commands :

ALTER TABLE `invoices`.`invoices`
    CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT 0;

ALTER TABLE `invoices`.`invoices`
    CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Note: This will work only in MySQL and not on SQL Server

edited Jan 24 '17 at 06:18

answered Oct 12 '15 at 07:49

Abhishek Goel

18,785
11
87
65

Have you tried them yourself? They both yield SQL syntax errors. – nevvermind Feb 23 '16 at 11:08
1

Yes I have tried. DateTime is column name. please check properly – Abhishek Goel Feb 23 '16 at 12:30

score 1 · Answer 3 · edited May 23 '17 at 12:18

1

For your use case I think you would be better served with DATETIME, eg:

ALTER TABLE `my_table`
    CHANGE `my_col` `my_col` DATETIME NOT NULL DEFAULT NOW();

This will default to NOW() on insert, but remain unaffected on update.

See this question for a good explanation of the difference: Should I use field 'datetime' or 'timestamp'?

edited May 23 '17 at 12:18

Community

1
1

answered Jan 23 '17 at 10:38

zero-day

362
2
13

score 0 · Answer 4 · answered Aug 22 '17 at 22:58

0

ALTER TABLE `invoices` CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'

or

ALTER TABLE `invoices` CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

answered Aug 22 '17 at 22:58

Anthony Awuley

3,455
30
20

While answers are always appreciated, this question was asked 6 **years** ago, and already had an accepted solution. Please try to avoid 'bumping' questions to the top by providing answers to them, unless the question was not already marked as resolved, or you found a new and improved solution to the problem. Also remember to provide some [**context surrounding your code**](https://meta.stackexchange.com/questions/114762) to help explain it. Check out the documentation on [**writing great answers**](http://stackoverflow.com/help/how-to-answer) for some tips on how to make your answers count :) – Obsidian Age Aug 23 '17 at 02:11

Remove on update condition without drop mysql table

4 Answers4