Insert or update if column with specific value exists

Question

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?

Does this answer your question? UPDATE if exists else INSERT in SQL — PaulS
– PaulS, Commented Oct 27, 2021 at 11:13
@PaulS No, It's the same statement I tried and didn't work as expected — user_411
– user_411, Commented Oct 27, 2021 at 11:26
Does this answer your question? "INSERT INTO or UPDATE with two conditions", "IF EXISTS UPDATE ELSE INSERT INTO" — outis
– outis, Commented Oct 27, 2021 at 19:12

ThangLeQuoc · Accepted Answer · 2021-10-27 11:40:51Z

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

edited Oct 27, 2021 at 11:40

answered Oct 27, 2021 at 11:35

ThangLeQuoc

3,4882 gold badges28 silver badges38 bronze badges

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

9 Comments

user_411 Over a year ago

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

ThangLeQuoc Over a year ago

same user has multiple questions -> isn't that lead to different question_id(s) for a same user_id?

user_411 Over a year ago

Yes exactly. question_id and user_id can't be duplicated in 2 rows.

ThangLeQuoc Over a year ago

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.

user_411 Over a year ago

Yes. But as I know there is no unique option for something like that. Unique for columns. Not for 2 at the same time

|

Collectives™ on Stack Overflow

Insert or update if column with specific value exists

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related