Needed: mysql current timestamp on update also if UPDATE does not change values in table

3
votes

I have a database with 3 columns:

ID / Price / last_updated

The column "last_updated" has ..

  • as type: timestamp
  • as Standard: CURRENT_TIMESTAMP
  • as Attribute: CURRENT TIMESTAMP ON UPDATE
  • as Extra : CURRENT TIMESTAMP ON UPDATE

when I run a mysql_query(UPDATE...) to change the prices, some prices do not change. In those cases, however, the last_updated value remains the same than before (which in this case is: 0000-00-00 00:00:00 as the records have not been changed).

So I guess, UPDATE only "updates" if the new value is different from the current one in place.

How can I get the current timestamp put into the table, even if the value is not changed after performing a mysql_query(UPDATE...) ?

2
mysqltimestamp

2 Answers

4
votes

In your query, just always update the last_updated field using NOW(). Like this:

UPDATE table SET price = ?, last_updated = NOW() WHERE id = ??
0
votes

TRy this :

ALTER TABLE myTable 
MODIFY last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

For a one time fix, on the existing table :

update myTable set last_updated=now() where last_updated='0000-00-00 00:00:00'