CODEWITHSUNDEEP
mysqldatabasesql-updatesql-insert
Wesley
Wesley

Reputation: 925

Insert into table or update if exists multiple unique index (MySQL)

I'm having a issue where I want to INSERT values to a table if the combination (poll_id, user_id, question_id) does not exists, else I want to update the column answer.

The table is as follows:

poll_results poll_id - int(11) | user_id - int(11) | question_id - int(11) | answer - varchar(100)

I've set the unique to (poll_id, user_id, question_id) using:

ALTER TABLE poll_results ADD UNIQUE INDEX unique_index(poll_id, user_id, question_id);

When I run the following query it will first insert the first values set and then it updates the answer field with the value from the second value set.

INSERT INTO poll_results (poll_id, user_id, question_id, answer) VALUES(1, 1, 1, 'User 1'),(1, 2, 1, 'User 2') ON DUPLICATE KEY UPDATE answer=VALUES(answer)

What I see is:

poll_id - user_id - question_id - answer

1 | 1 | 1 | User 1

What I want to see is:

poll_id | user_id | question_id | answer

1 | 1 | 1 | User 1

1 | 2 | 1 | User 2

How can I achieve the end result?

Upvotes: 1

Views: 1576

Answers (1)

tadman
tadman

Reputation: 211680

The way the ON DUPLICATE KEY feature works is on the trigger of any UNIQUE type indexes, not just the one you intend it to trigger on.

Remove any indexes that might be interfering with this and you should be fine.

Upvotes: 2

Related Questions