List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 2 1999 8:12pm
Subject:Re: TIMESTAMP changes when no other fields have change
View as plain text  
At 12:32 PM -0600 4/2/1999, Fred Lindberg wrote:
>On Fri, 2 Apr 1999 09:48:05 -0800, Mike Wexler wrote:
>
>>	INSERT INTO myTable(value, id) VALUES ("my value", "myKey");
>>	SELECT stamp FROM myTable WHERE id="myKey";
>>
>>	# Time consuming operation here
>>
>>	UPDATE myTable SET value="my value" WHERE id="myKey";
>>	SELECT stamp FROM myTable WHERE id="myKey";
>
>Why do you call this a bug? You are clearly updating the record, even
>though you're putting in a value that happens to be the same as the one
>that was there. There is no reason for the SQL server to look at
>"value" in the record to satisfy your request. It simply finds records
>matching the WHERE part and places "my value" into the field "value".
>
>If you want the time stamp updated only on change, you need to do a
>SELECT, identify the records where you want to change, and then change
>only those.

If the value actually is the same one as the value that's already in
the table, then according to the manual it really is a bug.  This
comes from the UPDATE section:

Automatic updating of the first @code{TIMESTAMP} column occurs under any of
the following conditions:

@itemize @bullet
@item
The column is not specified explicitly in an @code{INSERT} or
@code{LOAD DATA INFILE} statement.
@item
The column is not specified explicitly in an @code{UPDATE} statement and some
other column changes value.  (Note that an @code{UPDATE} that sets a column
to the value it already has will not cause the @code{TIMESTAMP} column to be
updated, because if you set a column to its current value, @strong{MySQL}
ignores the update for efficiency.)
@item
You explicitly set the @code{TIMESTAMP} column to @code{NULL}.
@end itemize

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
Thread
TIMESTAMP changes when no other fields have changeMike Wexler2 Apr
Re: TIMESTAMP changes when no other fields have changeFred Lindberg2 Apr
  • Re: TIMESTAMP changes when no other fields have changeMike Wexler)2 Apr
  • Re: TIMESTAMP changes when no other fields have changePaul DuBois2 Apr
    • Re: TIMESTAMP changes when no other fields have changeMichael Widenius3 Apr