insert and update in one query using multiple keys

Question

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'

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.

hjpotter92 · Accepted Answer · 2015-10-09 06:39:00Z

2

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

edited Oct 9, 2015 at 6:39

answered Oct 8, 2015 at 17:02

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

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

8 Comments

Rebecca Over a year ago

i need to make both phonecode and value to match, that is if the insert value matches the phonecode and the value it should update or insert... this is just matching my first value that is the phonecode.. and these values can be duplicated for only one row. I am actually using this for phonecodes and phone numbers, phone numbers can be similar in different countries but in this case the phone code changes or vice versa if phonecode is same but phone numbers differ so in this i need to check both phonecode and phone number and insert if existence is zero...

hjpotter92 Over a year ago

@Rebecca I understand. That is what the multicolumn UNIQUE index will do.

Rebecca Over a year ago

ALTER TABLE yourTable ADD UNIQUE INDEX idx_ph_val (phonecode, value); INSERT INTO yourtable (name, phonecode,value) VALUES ('bar',300,'300') ON DUPLICATE KEY UPDATE name='hello'; Is this correct ?

hjpotter92 Over a year ago

The ALTER TABLE statement needs to be run just once. It'll update your table. Next time onwards, you only have to do

INSERT INTO yourtable (name, phonecode,value) VALUES (something, else, goes-here) ON DUPLICATE KEY UPDATE name='wheeee!!';

hjpotter92 Over a year ago

@Rebecca It is working exactly as expected here: sqlfiddle.com/#!9/be84b/1 Maybe you have some other error?

|

Collectives™ on Stack Overflow

insert and update in one query using multiple keys

1 Answer 1

EDIT

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related