CODEWITHSUNDEEP
mysql
Max Koretskyi
Max Koretskyi

Reputation: 105563

ON DUPLICATE KEY UPDATE update with new values

I have simple table onupdate with two columns: ID and Content and one record: [1, 'old content']. I want to replace content with new content when adding a record [1, 'new content']. Is it possible to do with ON DUPLICATE KEY UPDATE syntax? By doing in this way the old value remains:

INSERT INTO onupdate (id, content) VALUES (1, 'newcontnet')
ON DUPLICATE KEY UPDATE
  onupdate.content = onupdate.content

The the example is for illustration purposes only. Please don't propose UPDATE query since I don't know if ID exists.

Upvotes: 1

Views: 174

Answers (2)

Quassnoi
Quassnoi

Reputation: 425863

INSERT
INTO    onupdate
        (id, content)
VALUES  (1, 'newcontnet')
ON DUPLICATE KEY
UPDATE  content = VALUES(content)

VALUES(content) means "value that would be there if INSERT took place"

Upvotes: 2

Dan
Dan

Reputation: 10786

Saying ON DUPLICATE KEY UPDATE onupdate.content = onupdate.content is like saying "If id is already there, take content and replace it with itself". You presumably want to put your new data in its place:

INSERT INTO onupdate (id, content) VALUES (1, 'newcontnet')
ON DUPLICATE KEY UPDATE
  onupdate.content = 'newcontnet'

Upvotes: 3

Related Questions