CODEWITHSUNDEEP
mysqlsql
user3904830
user3904830

Reputation:

mysql- How to Update a record only under a specific condition?

I have this table:

ID NAME age TIME
1  Mike 23  10
2  Kyle 24  20

Name is Unique

sometimes when I want to Insert a new record, I want to replace the old one by a new one not inserting a new record

for example if I have this record:

1  Mike 23  10

and I want to insert this record :

1 Mike2 23 25

I replace the older one by the new one:

ID NAME age TIME
1  Mike2 23  25
2  Kyle 24  20

I do it by INSET INTO TABLE(NAME) VALUES('newName') ON DUPLICATE KEY UPDATE NAME='newName';

BUT what if I want to replace it only if the newTIME is bigger than the old time, for example:

for example if I have this record:

1  Mike 23  10

If the new record TIME is 25 we replace the record:

1  Mike2 23  25
2  Kyle 24  20

If the new record TIME is 2 we don't replace the record:

1  Mike 23  10
2  Kyle 24  20

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you can do this with can put a conditional expression in the on duplicate key update clause:

INSERT INTO TABLE(NAME, time)
    VALUES('newName', time)
    ON DUPLICATE KEY
        UPDATE NAME = (case when values(time) > time then values(name) else name end),
               time = (case when values(time) > time then values(time) else time end);

Note: the query in your question doesn't have a time in the insert, but it is implied by your question.

Upvotes: 1

John Lockwood
John Lockwood

Reputation: 3875

Have you tried something more or less along the lines of:

"UPDATE table_name set TIME = newtime where name = 'Mikes' and newtime > TIME"

?

Upvotes: 0

Related Questions