CODEWITHSUNDEEP
mysql
Rebecca
Rebecca

Reputation: 413

insert and update in one query using multiple keys

I am trying to find a query that would update if it is a duplicate entry or insert if not. I tried various solutions but nothing works please help me with the same.

My Code is : 

INSERT INTO yourtable (name, phonecode,value)
VALUES ('bar',91,'300')
ON DUPLICATE KEY UPDATE name='dgdgf'

enter image description here

Here the issue comes up is my phonecode and value is the key that needs to be checked for the insert or update but making any of them primary would allow duplicate entry and unique would not allow me to a duplicate entry if any one of the row data is same.

Upvotes: 0

Views: 111

Answers (1)

hjpotter92
hjpotter92

Reputation: 80639

Put a multicolumn UNIQUE index:

ALTER TABLE yourTable
ADD UNIQUE INDEX idx_ph_val (phonecode, value)

Now, after the above ALTER statement is executed, when you try inserting a duplicate row, the ON DUPLICATE KEY UPDATE will perform just as you desire.

EDIT

SQLFiddle link, as requested in comments: http://sqlfiddle.com/#!9/be84b/1

Upvotes: 2

Related Questions