File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Timestamp update not requested in MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Timestamp update not requested in MySQL" Watch "Timestamp update not requested in MySQL" New topic

Timestamp update not requested in MySQL

Matteo Di Furia
Ranch Hand

Joined: Jun 20, 2008
Posts: 102
Hello guys,
I had a problem I managed to resolve somehow, but I would like to know if anyone faced this before and if they can explain me what's happening (I solved it, but still can't understand why it happens).

Basically, I have a table in which I store topics, each topic has, among the others, fields TOPIC_NUM_VIEWS and TOPIC_DATE (this is actually a timestamp).
Each time the topic is shown by a user, the field TOPIC_NUM_VIEWS gets incremented by 1. I accomplished this with the SQL statement :

This is actually incrementing the counter, but unexpectedly it also updates the timestamp storing the actual moment in which I adjust the counter.

After reading this I managed to solve this modifying the SQL statement like this :

which actually increments the counter and leaves the timestamp unmodified, still puzzles me about why I must use such a strange query to NOT modify a field. Reading the post from Scott Selikoff made me think about something like doing an update with a null new value for the timestamp field (that's why I tried to use "TOPIC_DATA=TOPIC_DATA", like saying "topic data should get its old value" in a strange way).

As I stated, it works, but I still wonder why it happens, maybe there is a cleaner solution (or maybe this can help someone else).

[ July 02, 2008: Message edited by: Matteo Di Furia ]
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

TOPIC_DATA in your examples refers to TOPIC_DATE in your original description of the table, correct?

Is it possible that there's a trigger on the table that updates TOPIC_DATE any time a row is updated? If so, and if you find you don't actually want that to happen, you should change something. Perhaps the trigger should only fire on inserts and not on updates, for example. Just guessing.
[ July 02, 2008: Message edited by: Paul Clapham ]
Matteo Di Furia
Ranch Hand

Joined: Jun 20, 2008
Posts: 102
The column name is actually TOPIC_DATA ("data" is the italian word for "date"), but there's no triggers at all defined on the DB. Every update or delete on tables happen only via application submitted queries.
I agree. Here's the link:
subject: Timestamp update not requested in MySQL
It's not a secret anymore!