MySQL Insert Into and on Duplicate Key query is not updating

Question

My table before the insert (Table name is ds_answer)

===============================================================================

answer_id  |   member_id  |  question_id | msgstr | type  |        tstamp

===============================================================================
   50            2             176          aaaa     show   2014-07-27 12:11:12
   51            2             177          bbbb     show   2014-07-27 12:12:23
   52            2             180          cccc     show   2014-07-27 12:12:50
===============================================================================

Query command ::

INSERT INTO `ds_answer` (`member_id`, `question_id` , `msgstr` , `type`)
  VALUES ('2', '180' , 'dddssds' , 'show' )
  ON DUPLICATE KEY UPDATE msgstr = 'dddssds'

It's not working. MySQL added row every time. But It isn't update msgstr column in answer_id = 52.

How to fix this problem?

do you actually have a unique index to trigger the on duplicate stuff? insert ... on duplicate doesn't check if there actually is a unique index. it'll try the insert no matter what, but the on dupe stuff just won't ever trigger. — Marc B
– Marc B, Commented Jul 30, 2014 at 16:45

Marcus Adams · Accepted Answer · 2014-07-30 16:51:56Z

1

In order for ON DUPLICATE KEY UPDATE to work, there has to be a unique key (index) to trigger it. Create a unique key on the column or columns that should trigger the ON DUPLICATE KEY UPDATE.

Going by your sample insert statement, it seems like you want a multi-column unique index on: (member_id, question_id).

Something like this:

ALTER TABLE ds_answer
  ADD UNIQUE INDEX `ui_member_question` (member_id, question_id);

edited Jul 30, 2014 at 16:51

answered Jul 30, 2014 at 16:41

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

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

2 Comments

Barmar Over a year ago

My guess is he needs a unique composite index on (member_id, question_id) -- show the command for adding this.

Hacker Dewdie Over a year ago

Thank you for this answer. this answer is help me ^^

Community · Accepted Answer · 2017-05-23 10:33:31Z

0

As @therefromhere says here.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

You need to use a unique key to make it work

Have a look at the documentation http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jul 30, 2014 at 16:45

Avinash Babu

6,2534 gold badges23 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL Insert Into and on Duplicate Key query is not updating

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related