1

I have the following table:

           questions_answers
 _____________________________________
| id | question_id | answer | user_id |
|____|_____________|________|_________|
|  1 |      1      |   yes  |    3    |
|____|_____________|________|_________|

I want to check if question_id and user_id exist, Then update the existing row.

I tried this command INSERT INTO questions_answers (question_id, answer, user_id) VALUES (1, 'no',3) ON DUPLICATE KEY UPDATE answer = 'no'

So in this case there is a question_id = 1 and user_id = 3, Hence it should update that row and not insert a new row.

But I think it checks the id column for dublicates.

Is there is a way to get this done with SQL or I need to check if row exists with PHP, then update?

user_411
  • 67
  • 6
  • Does this answer your question? [UPDATE if exists else INSERT in SQL](https://stackoverflow.com/questions/30569666/update-if-exists-else-insert-in-sql) – PaulS Oct 27 '21 at 11:13
  • You can try to use MERGE – Sergey Oct 27 '21 at 11:13
  • @PaulS No, It's the same statement I tried and didn't work as expected – user_411 Oct 27 '21 at 11:26
  • 1
    Does this answer your question? "[INSERT INTO or UPDATE with two conditions](https://stackoverflow.com/q/32468456/90527)", "[IF EXISTS UPDATE ELSE INSERT INTO](https://stackoverflow.com/q/15383852/90527)" – outis Oct 27 '21 at 19:12

1 Answers1

1

Your INSERT INTO .. ON DUPLICATE KEY UPDATE is not working because the current KEY that determine the duplication/violation is the id column primary key.

From the spec of this syntax

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

To achieve this, you will need to create a UNIQUE CONSTRAINT on two columns question_id and user_id. This unique constraint will raise upon the duplication of question_id - user_id pair, and triggers the UPDATE statement as you intends.

ALTER TABLE questions_answers ADD CONSTRAINT uq_user_question UNIQUE KEY(question_id ,user_id);

Reference:
MySQL Insert On Duplicate
MariaDB - INSERT ON DUPLICATE KEY UPDATE

ThangLeQuoc
  • 2,272
  • 2
  • 19
  • 30
  • Makes sense. But I can't make these columns unique, As the same user would have multiple questions. So `user_id` and `question_id` would be duplicated. But not the same values – user_411 Oct 27 '21 at 11:41
  • same user has multiple questions -> isn't that lead to different `question_id`(s) for a same `user_id`? – ThangLeQuoc Oct 27 '21 at 11:43
  • Yes exactly. `question_id` and `user_id` can't be duplicated in 2 rows. – user_411 Oct 27 '21 at 11:48
  • So doesn't that make the `question_id` and `user_id` pair always unique? Meaning one user can only have one single answer (record) on one question. – ThangLeQuoc Oct 27 '21 at 11:51
  • Yes. But as I know there is no unique option for something like that. Unique for columns. Not for 2 at the same time – user_411 Oct 27 '21 at 11:54
  • I'm sorry that I'm not too sure what you're trying to explain. I would suggest you just give it a try and observe the behavior change then. – ThangLeQuoc Oct 27 '21 at 11:57
  • I think that you're confused the constraint on multiple columns. If you add the unique constraints on two column `(question_id, user_id)`, the existing values `1,1`; `2,1`; `3,1` will not be an issue, but if the same `question_id` and `user_id` record is inserted again, ex: `1,1`, the constraint violation on unique `question_id` AND `user_id` pair will be thrown. Just give it a try, don't worry, trust me :) . If somehow it does not work as you expected, you can quickly drop the constraint any time. – ThangLeQuoc Oct 27 '21 at 12:12
  • This is the 1st time I hear about unique constrain for 2 columns at the same time. I don't want to show an error. I want to insert or update if `question_id` and `user_id` matched – user_411 Oct 27 '21 at 12:56
  • It WILL NOT show error if you use it with your existing INSERT ON DUPLICATE KEY update. What I suggest you to try is: Create a single unique constraint on two columns question_id and user_id. Then use your INSERT … ON DUPLICATE KEY statement as usual. Please at least give it a try. – ThangLeQuoc Oct 27 '21 at 13:37