0

I took a look at many questions similar to mine, but I didn't get what I'm looking for, maybe you guys can help me

I have this table:

enter image description here

What I want to do is:

  • Insert a new record (regardless whether "user_id" or "course_id" are already exist or not).
  • BUT!, if there is a record with the same "user_id" and "course_id" and "tutorial_id", then just update "tutorial_id" and "tutorial2_id" and leave the rest as they are.

I don't want to declare column "tutorial_id" as UNIQUE, because more than a user can have the same "tutorial_id" (as you can see in the above picture).

In addition, ON DUPLICATE KEY UPDATE didn't work for me.

I'm thinking of using QUERY two times, one to select and check if record exist, and the other one whether to UPDATE or INSERT, but is that correct?

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
iHam9
  • 45
  • 1
  • 8
  • Show your `ON DUPLICATE KEY UPDATE` usage. You can make the pair of the three unique, so not just `tutorial_id` would be unqiue. e.g. `ALTER TABLE TABLE_NAME add CONSTRAINT UQ_user_course_tut UNIQUE (user_id, course_id, tutorial_id)` – chris85 Mar 26 '17 at 18:57
  • Then you need to create the unique key across those three columns and then the on duplicate key update will work. – Niagaradad Mar 26 '17 at 18:57
  • *In addition, ON DUPLICATE KEY UPDATE didn't work for me.* why? What is your `PRIMARY KEY`. Are `user_id` AND `course_id` a unique combination? –  Mar 26 '17 at 18:58
  • *"I'm thinking of using QUERY two times, one to select and check if record exist, and the other one whether to UPDATE or INSERT, but is that correct?"* - See this Q&A then http://stackoverflow.com/q/22252904/1415724 – Funk Forty Niner Mar 26 '17 at 19:04
  • Thank you guys, making three column unique is something new to me, it worked. – iHam9 Mar 28 '17 at 20:47

2 Answers2

0

Use INSERT ... ON DUPLICATE KEY UPDATE ... syntax. For it to work.

If your user_id and course_id combination is unique you could delete the id field from your table and make those two fields a primary key.

In any other case that the id field is also needed and makes a unique combination of the three for each record then make those three fields the primary key for you table (id,user_id and course_id).

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
0

How about issuing the UPDATE first, and if no records are affected (using row_count() then INSERT? This way you only test the existence condition once.

rextester demo

update test set tutorial2_id = @tutorial2
where user_id = @user_id and course_id = @course_id and tutorial_id = @tutorial_id;

insert into test (user_id, course_id, tutorial_id, tutorial2_id)
select @user_id, @course_id, @tutorial_id, @tutorial2_id)
where row_count() = 0;
Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14