MySQL: If values exist do an update on the row, if values do not exist do an insert

Question

I need an sql statement that will update if the row exists and insert if the row doesn't exist. I have a table with the following values...

id is (Unique + auto increment)

id | selectedID | userID  | question
------------------------------------
1  |    1       |   3     |   10

if the user with userID 3 selects a different selectedID e.g. 2 for question 10 i want the table to update to:

id | selectedID | userID  | question
-----------------------------------
1  |    2       |   3     |   10

And if the user with userID 3 selects selectedID 2 again for question 10 i don't want this to insert another row.

If the user with id 3 selects a selectedID for question 11 I want it to insert a new row e.g. 2 for question 11:

id | selectedID | userID  | question
-----------------------------------
1  |    2       |   3     |   10
2  |    2       |   3     |   11

What is your question? Where's your code? Where are you stuck? Please read: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) and also [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) — M. Eriksson, Feb 16 '19 at 15:28

score 1 · Accepted Answer · answered Feb 16 '19 at 15:30

You are looking for insert on duplicate key update.

Basically, you want the combination (userId, questionId) to be unique. First set up a unique constraint/index:

create unique index unq_t_userId_questionId on t(userId, questionId);

Then use on duplicate key update for your inserts:

insert into t (userId, questionId, selectedId)
    values (@userId, @questionId, @selectedId)
    on duplicate key update selectedId = values(selectedId);

Worked perfectly thanks @GordonLinoff! – Feb 17 '19 at 15:20 — , Feb 17 '19 at 15:20

score 0 · Answer 2 · answered Feb 16 '19 at 15:36

INSERT on duplicate key

You can use above construct in mysql which will update the record if key is present, if primary key not found makes an insert. Somewhat MERGE does in oracle.

If you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old row based on the new values.

MySQL: If values exist do an update on the row, if values do not exist do an insert

2 Answers2