Insert or Update with mysql if same two column

Question

I have a table to store client's answers.I want to use one mysql query to insert or update this table. My Table name : questionform_answer and columns > ClientID QuestionID OptionID

Each client can only have one same question id.For example

 ClientID QuestionID OptionID
    1         1         1
    1         2         5
    2         1         3

I want to update OptionID if already exist ClientID and QuestionID.I don't want to use select query so taking so time. I tried

     ON KEY UPDATE
     Replace Into

But I could not. I use php so I tried first update query and if mysqli return fail insert row but it is also slow. MY insert and update code :

   Insert Into questionform_answer (ClientID,QuestionID,OptionID) values 
   ('$ClientID','$soruid','$cevapid')


   Update questionform_answer set OptionID='$cevapid' where 
   ClientID='$ClientID' and QuestionID='$soruid'

score 0 · Accepted Answer · answered Sep 09 '19 at 08:03

0

One way around this is to add a unique key over (ClientID, QuestionID) and use an INSERT ... ON DUPLICATE KEY UPDATE query:

ALTER TABLE table1
ADD UNIQUE INDEX (ClientID, QuestionID);

INSERT INTO table1
VALUES (1, 1, 4)
ON DUPLICATE KEY UPDATE
OptionID = VALUES(OptionID)

Demo on dbfiddle

answered Sep 09 '19 at 08:03

Nick

138,499
22
57
95

How can I delete Duplicate entry for this client id and question id ? @Nick – johnny003 Sep 09 '19 at 09:45
@johnny003 do you have existing duplicates in the table? – Nick Sep 09 '19 at 09:47
Yes.I want to use old table with record but existing dublicates @Nick – johnny003 Sep 09 '19 at 09:49
@johnny003 this should help: https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql – Nick Sep 09 '19 at 09:51
OKey I remove it with exel – johnny003 Sep 09 '19 at 09:53

score 0 · Answer 2 · answered Sep 09 '19 at 08:04

First of all, you should use prepared statements to avoid SQL injections. If you have a unique key on (ClientID,QuestionID), you can do INSERT INTO ... ON DUPLICATE KEY like this:

INSERT INTO questionform_answer (ClientID,QuestionID,OptionID) 
            values ('$ClientID','$soruid','$cevapid') 
on duplicate key update OptionID='$cevapid'

Insert or Update with mysql if same two column

2 Answers2