MySQL on duplicate - Insert but do not update

Question

I have an SQL query that looks like this (WordPress options).

INSERT INTO wp_options (option_name, option_value, autoload) VALUES
(
    'category_children',
    'a value',
    'yes'
),
(
    'test_options',
    'testing',
    'yes'
)
ON DUPLICATE KEY UPDATE
    option_name=option_name,
    option_value=option_value,
    autoload=autoload
;

What is does

It inserts the values correctly but if I run it again with different values I expect it to update the values to the new ones.

What it don't do

It does not update the values the second time I run the query. I change the values. I get SQL no errors. 0 rows affected.

What it should to

If I change "a value" to "some other value" and run the query the value should be replaced if category_children already exists.

Question

What I'm I doing wrong? I use mysql 5.5.24.

Maybe duplicated of http://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query — Jason Heo, Nov 25 '13 at 10:28

score 3 · Accepted Answer · answered Nov 25 '13 at 10:30

Updating option_name to option_name will have no effect. You can use the VALUES syntax to update to the value from the insert:

INSERT INTO wp_options (option_name, option_value, autoload) VALUES
(
    'category_children',
    'a value',
    'yes'
),
(
    'test_options',
    'testing',
    'yes'
)
ON DUPLICATE KEY UPDATE
    option_name=VALUES(option_name),
    option_value=VALUES(option_value),
    autoload=VALUES(autoload)
;

MySQL on duplicate - Insert but do not update

1 Answers1