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 stackoverflow.com/questions/2714587/…

Jason Heo
– Jason Heo

2013-11-25 10:28:24 +00:00
Commented Nov 25, 2013 at 10:28 — Jason Heo
– Jason Heo, Commented Nov 25, 2013 at 10:28

Jim · Accepted Answer · 2013-11-25 10:30:16Z

3

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)
;

answered Nov 25, 2013 at 10:30

Jim

22.7k6 gold badges54 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL on duplicate - Insert but do not update

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related