mySQL help

[Moose.]

Morning all,

Another poser for you. I need to modify a TIMESTAMP field to only get the current timestamp when the record is created and NOT when it's updated as it's currently doing. Reading the mySQL documentation it seems I could have done it when the table was created but I just can't figure out how to modify the column. I've tried:

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14) DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE 'NO ACTION';

but it's not liking the "ON UPDATE" bit. Any ideas?

[selwonk]

Try:

ON UPDATE CURRENT_TIMESTAMP

There *was* as bug with this in some versions of MySQL, but it's worth a try...

[selwonk]

Or does that make it auto-updating?

[selwonk]

Have you tried:

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14) DEFAULT CURRENT_TIMESTAMP ON UPDATE NO ACTION;

[Moose.]

Cheers for the suggestions. Your first one would indeed make it take the current time stamp on update. I've tried loads of combinations but they all complain which errors like:

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 'ON UPDATE 'NO ACTION'' at line 1

mySQL seems to not like the ON UPDATE command. Perhaps the version of mySQL (4.0.24) is too old?

[selwonk]

Have you tried it without the speech marks as above?

[Moose.]

selwonk said:

Have you tried it without the speech marks as above?

Yup, gives:

ERROR 1064 (00000): 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 'CURRENT_TIMESTAMP ON UPDATE NO ACTION' at line 1

also tried:

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14)DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE 'NO ACTION';

which gives:

ERROR 1064 (00000): 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 'ON UPDATE 'NO ACTION'' at line 1

Edited by Moose. on Thursday 22 May 11:50

[LivinLaVidaLotus]

Nope, you've not done as he said, try it without the quotation marks i.e.

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14)DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE NO ACTION;

[selwonk]

LivinLaVidaLotus said:

Nope, you've not done as he said, try it without the quotation marks i.e.

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14)DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE NO ACTION;

Makes no odds by the look of things

[selwonk]

Any reason you can't create a new TIMESTAMP column and then copy the old one across? You could create the new column with the NO UPDATE options set?

[Moose.]

selwonk said:

Any reason you can't create a new TIMESTAMP column and then copy the old one across? You could create the new column with the NO UPDATE options set?

Thought of that, but to create a new column requires ALTER TABLE which doesn't seem to like the ON UPDATE command. Aaaaarrrggg!

[Moose.]

LivinLaVidaLotus said:

Nope, you've not done as he said, try it without the quotation marks i.e.

ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14)DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE NO ACTION;

mysql> ALTER TABLE `database`.`Customers` CHANGE `StartDate` `StartDate` TIMESTAMP(14)DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE NO ACTION;
ERROR 1064 (00000): 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 'ON UPDATE NO ACTION' at line 1
mysql>

[selwonk]

Sorry mate - I can't get it to work either