PHP MySQL INSERT ON DUPLICATE KEY UPDATE not working

Question

My query:

INSERT INTO `table` (`article_id`, `score_count`) VALUES (1922, '{\"1\":3,\"2\":2,\"3\":10,\"4\":2,\"5\":1}') ON DUPLICATE KEY UPDATE `article_id`= 1922

And my article_id column is set as primary unique key. After I run this I get 0 rows inserted and no update.

Can you add your table definition please.

P.Salmon
– P.Salmon

2018-12-07 16:34:52 +00:00
Commented Dec 7, 2018 at 16:34 — P.Salmon
– P.Salmon, Commented Dec 7, 2018 at 16:34
Is table can be used as table name in MySQL?

flyingfox
– flyingfox

2018-12-07 16:35:25 +00:00
Commented Dec 7, 2018 at 16:35 — flyingfox
– flyingfox, Commented Dec 7, 2018 at 16:35

maio290 · Accepted Answer · 2018-12-07 16:56:47Z

2

INSERT INTO `table` (`article_id`, `score_count`) 
VALUES (1922, '{\"1\":3,\"2\":2,\"3\":10,\"4\":2,\"5\":1}') 
ON DUPLICATE KEY 
UPDATE `score_count`= '{\"1\":3,\"2\":2,\"3\":10,\"4\":2,\"5\":1}'

Since you don't want to update the primary key to itself.

ON DUPLICATE KEY UPDATE updates the specified column to a value, if a duplicate key was found. You were updating article_id which was already 1922 to 1922. See the offical reference.

edited Dec 7, 2018 at 16:56

answered Dec 7, 2018 at 16:44

maio290

6,8081 gold badge27 silver badges41 bronze badges

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

5 Comments

Blue Over a year ago

Even though this is the correct answer, can you clarify why OP was updating the primary key to itself? What does this change?

maio290 Over a year ago

Do you really think that this is necessary for such a simple case? Imho it's pretty clear if you compare the statements ;)

Blue Over a year ago

It is if you want to make a truly complete answer, and have my upvote. I personally would have explained to OP, that the update statement is not what row that you're trying to update, but it's the actual columns that need updating (So in this case, he should be attempting to update the score_count field instead).

maio290 Over a year ago

I hope you're now satisfied ;)

Blue Over a year ago

I am: This is now a much more complete answer, and explains the reason why OP was having issues. These are the types of answers that are more useful to future readers who happen upon this question in the future. It may take a bit more effort, but in the end, it's quality questions and answers that drive users to this site.

Collectives™ on Stack Overflow

PHP MySQL INSERT ON DUPLICATE KEY UPDATE not working

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related