CODEWITHSUNDEEP
mysqlsql
Hacker Dewdie
Hacker Dewdie

Reputation: 321

MySQL Insert Into and on Duplicate Key query is not updating

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?

Upvotes: 0

Views: 44

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

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

Upvotes: 1

Avinash Babu
Avinash Babu

Reputation: 6252

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

Upvotes: 0

Related Questions