Update Timestamp

[yaroslav]

I have a simple table with two rows. First row type is INT, second is Timestamp.
If i make update row with type INT, row with type Timestamp update automaticaly with NOW() value.
For example: "UPDATE TestTable SET IntField = 5"
With this query i change 2 record(IntField and TimestampField).
Why it so, if i want to change only row with type INT ?
Any suggestions ?

 

[phpPete]

UPDATE TestTable SET IntField = 5 WHERE IntField = X

Here X is a number already stored in IntField. Be Advised that if the number you give as X occurs more than once, then IntField will be set to the new value for each occurance of X.

Your query tells MySQL to update EVERY IntField column by changing the existing value to 5.

You should add a column, say test_id, make it auto-increment, primary key, then you can update based on the test_id value, which will be unique

Example;

UPDATE TestTable SET IntField = 5 WHERE test_id = X

 

[yaroslav]

No you wrong understand me, look:
I have a table: TestTable
__________________________
| IntField | TimestampField |
|_______|______________|
| INT | Timestamp |


For example i have 1 record in this table:

| 5 | 19990813050505 |

Now execute command: UPDATE TestTable SET IntField = 7 WHERE IntField = 5

And what we get after this query ?

| 7 | 20020315200740 |

Look...TimestampField is changed..
Was 13-Aug-1999, now is 15-Mar-2002...

Every operations with INT field change Timestamp field..
Why, if i didn't say it in my query ?

(MySQL 3.23.36)

 

[piti]

as it is writen in mysql manual:

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself.

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
You explicitly set the TIMESTAMP column to NULL.
TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

 

[phpPete]

OK. type Timestamp stamps the current time every time that record is accessed.

So, I suggest you use type date, then input the date.

Are you using a scripting language like PHP or doing the input via the command line?

You could also set the field to varchar, then enter your date as a string: March 15, 2002 or 15/03/2002

 

[piti]

or you could use this

UPDATE TestTable SET IntField = 5, TimestampField = TimestampField WHERE conditions

 

[yaroslav]

Thank you piti..
This query solve my problem.

 

[petersJazz]

You can have two timestamps in a row, then the first one is utdated automatically, the other dont. Like:

id int
modified timestamp
created timestamp

But in this case you have to set the value of created when you insert the record:

insert into table (id, created) values (234, current_timestamp);