CODEWITHSUNDEEP
phpmysqlsqltimestamp
yogaraj.lingamoorthy
yogaraj.lingamoorthy

Reputation: 41

MYSQL ON UPDATE CURRENT_TIMESTAMP not updating when using INSERT OR REPLACE?

I unable to update current time(NOW) in last_updated cloumn.

Because i have read this query from text file.

INSERT OR REPLACE INTO table_name (kb_corporate_guid,kb_user_guid,
                                   name,last_updated) 
VALUES ('100','121','FAQ','2013-02-07 07:06:05');

I want to ignore last_updated cloumn value '2013-02-07 07:06:05' even if i specified in query and replace with NOW() value into last_updated cloumn.

I tried this one but it won't work for me.

ALTER TABLE table_name
   CHANGE `last_updated` `last_updated`  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP;

Upvotes: 1

Views: 1070

Answers (2)

Minesh
Minesh

Reputation: 2302

Option 1

You may create trigger on insert/update which will update last_updated field with MySql NOW() function, it will overwrite the field value, but it might slow down the process when you do bulk insert/update.

Option 2

Find and Replace the text for last_updated field and timestamp values from text file.

Option 3

Create temporary table with same schema and import into temporary table then use INSERT INTO main_table SELECT corp_id, user_id, name, NOW() FROM temp_table table to insert into main table.

Upvotes: 2

phpalix
phpalix

Reputation: 689

Default is used when the value is not sent in the query, use now in the query instead of timestamp..

Upvotes: 1

Related Questions