NOT automatically update timestamp field on Update query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

NOT automatically update timestamp field on Update query

Post by dimitris »

Hi all,

I have a table with some fields and a timestamp field! I want to update some fields from some rows but not the timestamp field which changes automatically.

How can i do that?
My actual MySQL query is this:

Code: Select all

UPDATE cars SET offer='No' WHERE registerdate<='20060615120000';
I was thinking of setting also the registerdate again by passing the previous values again but it is difficult to get a list of their current values and pass them with many update queries one by one :(
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Try this..

Code: Select all

ALTER TABLE `cars` CHANGE `registerdate` `registerdate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

astions, that won't work.
dimitris, solution is simple:

Code: Select all

UPDATE cars SET offer='No', registerdate=registerdate WHERE registerdate<='20060615120000';
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

Post by dimitris »

astions wrote:Try this..

Code: Select all

ALTER TABLE `cars` CHANGE `registerdate` `registerdate` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
What does that makes?
My problem is that the field auto updates without my will. http://sunsite.mff.cuni.cz/MIRRORS/ftp. ... ETIME.html

Someone says that by setting the field with its value doesn't change
if you need to use an UPDATE query but don't want
your timestamp columns to be automagically
updated as well just be sure to set them to
themselves in the SQL statement like so ...

UPDATE `table` SET `views` = `views`+1 ,
`timestamp` = `timestamp` WHERE `row_id`
= "242"

that way the the updates will take place but the
timestamps will not update themselves.
Hope it helps someone.
-=PJK=-
I tried
UPDATE cars SET offer='No', `registerdate`=`registerdate` WHERE registerdate<='$20060615120000';

but i get an error

Any ideas?
Last edited by dimitris on Mon Jul 17, 2006 4:36 am, edited 1 time in total.
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

Post by dimitris »

I fixed my whole query and it works! In my real query i had left open quotation marks (like my previous example)

SO SET registerdate=registerdate works

SOLVED
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Weirdan wrote:astions, that won't work.
dimitris, solution is simple:

Code: Select all

UPDATE cars SET offer='No', registerdate=registerdate WHERE registerdate<='20060615120000';
Why won't it work? It works for me. When I alter a table that way it removes the ON_UPDATE_CURRENT_TIMESTAMP. I'm using MySQL 4.1.16.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Why won't it work? It works for me. When I alter a table that way it removes the ON_UPDATE_CURRENT_TIMESTAMP. I'm using MySQL 4.1.16.
Ok, it would work for 4.1+. What about older servers?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

They need to be upgraded :lol:

Ok, on older servers I guess one would have to modify all the queries to take that into account like in your example, or just use more than 1 timestamp field so that it doesn't matter if it gets updated.. neither of which sound very fun..
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

Post by dimitris »

Weirdan wrote:astions, that won't work.
dimitris, solution is simple:

Code: Select all

UPDATE cars SET offer='No', registerdate=registerdate WHERE registerdate<='20060615120000';
Thanks quys! I missed your reply while i was testing the same!

Thanks all of you...

The solution which works for 4.1 + doesn't suit me as i am using 3.23 (!)

The problem is that i prefer in some queries to have this field auto update or auto insert while in others i prefer not to have this feature...
Post Reply