Issue with duplicate key update with existing values?

Question

I have a DB table that already has an existing value that I don't need to touch. When I try to call my SQL like so:

my table currently looks like this:

id  col1 col2
5   10   

$sql = "INSERT INTO sometable (id, col2) VALUES (5, 20) ON DUPLICATE KEY UPDATE id = 5";

I would expect the table to look like this after my insert:

id  col1 col2
5   10   20

Is there a way to accomplish this without using an update?

Answer 1

Assuming id is the key

This

INSERT INTO sometable (id, col2) VALUES (5, 20) 
ON DUPLICATE KEY UPDATE id = 5

Says to update the id field to 5 (UPDATE ID =5). Which would only work if the ID was 5 and makes no sense

What you want is either

INSERT INTO sometable (id, col2) VALUES (5, 20) 
ON DUPLICATE KEY UPDATE col2 = 20;

Or if you want to reference the value it would have been if there was no duplicate then use as Wilken suggested COL2 = VALUES(COl2)

DEMO

Answer 2

Check this out for a good example: MySQL: Insert On Duplicate

Answer 3

Use VALUES()

INSERT INTO sometable (id, col2) VALUES (5, 20)
   ON DUPLICATE KEY UPDATE col2 = VALUES(col2);