CODEWITHSUNDEEP
mysqldatabaseinsertsql-updateon-duplicate-key
Jens Törnell
Jens Törnell

Reputation: 171

MySQL on duplicate - Insert but do not update

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.

Upvotes: 0

Views: 1619

Answers (1)

Jim
Jim

Reputation: 22646

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

Upvotes: 3

Related Questions