[SOLVED] MySQL insert on duplicate update for non-PRIMARY key

Issue

I am little confused with insert on duplicate update query.
I have MySQL table with structure like this:

• record_id (PRIMARY, UNIQUE)
• person_id (UNIQUE)
• some_text
• some_other_text

I want to update some_text and some_other_text values for person if it’s id exists in my table.person or insert new record in this table otherwise. How it can be done if person_id is not PRIMARY?

Solution

You need a query that check if exists any row with you record_id (or person_id). If exists update it, else insert new row

IF EXISTS (SELECT * FROM table.person WHERE record_id='SomeValue')
    UPDATE table.person 
    SET some_text='new_some_text', some_other_text='some_other_text' 
    WHERE record_id='old_record_id'
ELSE
    INSERT INTO table.person (record_id, person_id, some_text, some_other_text) 
    VALUES ('new_record_id', 'new_person_id', 'new_some_text', 'new_some_other_text')

Another better approach is

UPDATE table.person SET (...) WHERE person_id='SomeValue'
IF ROW_COUNT()=0
    INSERT INTO table.person (...) VALUES (...)

Answered By – Lorenzo Vincenzi

Answer Checked By – Terry (BugsFixing Volunteer)