UPDATE + DATE_ADD updating all timestamp columns

[Saeven]

Hello,

Consider this table:


CREATE TABLE table_1 (
ID int(8) unsigned NOT NULL auto_increment,
date timestamp(8) NOT NULL,
anniversary timestamp(8) NOT NULL,
PRIMARY KEY (BID)
) TYPE=MyISAM;


With this entry:

198 | 20030328 | 20030105

Where I run this query:


UPDATE table_1 SET anniversary = DATE_ADD( anniversary, INTERVAL 12 MONTH ) WHERE BID = 198;


I'd expect this result:

198 | 20030328 | 20040105


But instead I get:

198 | 20040105 | 20050105

Notice the date column was updated as well as anniversary to today's date where I hadn't specified its alteration anywhere. Why is this? What can I do to avoid this?

Thanks.
Alex

[M Owen]

Try looking at what the timestamp type actually means ... Sounds to me like it's an auto update column ... BTW, what database is this in?

[tillu]

Which database system are you using? I thought you couldn't have more than one timestamp field in a table (atleast in in MS SQL Server).

I think you should be using datetime datatype. timestamp is auto updated field.