CODEWITHSUNDEEP
mysqlreplacetimestampdefault
Veljko
Veljko

Reputation: 1808

MySQL - stop updating DEFAULT CURRENT TIMESTAMP column

I have two date time columns in my database: inserted and updated

inserted timestamp DEFAULT CURRENT_TIMESTAMP 
updated   timestamp -------------------------

Primary column is ID.

I run SQL command:

REPLACE INTO TABLE1 (id,name,updated) VALUES ('1','test1',NOW())

And this is OK: inserted column get current time upon the first INSERT updated columns is also set to current time because it is set in REPLACE command.

If I run second command to update name, then updated column is being updated properly but also and INSERTED column is updated also. I do not want that!!! 

REPLACE INTO TABLE1 (id,name,updated) VALUES ('1','test2',NOW())

How to stop inserted column to be updated automatically once the REPLACE command is being run? What change on column INSERTED in mysql database should be done? I must use REPLACE sql or any synonym for my needs.

Upvotes: 1

Views: 1019

Answers (2)

mafirat
mafirat

Reputation: 287

If you want to update a field without write-in SQL command. You need to create that column like that: 

`inserted` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

But this one works for Update operations not Replace operations.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.6, “INSERT Syntax”.

Detailed explanations here, see: https://dev.mysql.com/doc/refman/8.0/en/replace.html

Upvotes: 1

juergen d
juergen d

Reputation: 204746

replace deletes the old record and inserts a new one.

If you want to update an existing record use update instead:

UPDATE TABLE1 
SET name = 'test2',
    updated = now()
where id = 1

Upvotes: 1

Related Questions