Update and add new records table with join in MySQL

Question

I have main table, mytable, with columns id,a,b,c,d and a Temp_TABLE with columns id,b,c

I want to update mytable with the values on temp Temp_TABLE, and if the records are not in mytable then insert them.

So far I have the following:

UPDATE mytable  
JOIN  
Temp_TABLE  
ON mytable.profileId = Temp_TABLE.profileId 
SET mytable.b = Temp_TABLE.b
    mytable.c = Temp_TABLE.c

But this only works for the first part.

How can I insert the the records from Temp_TABLE into mytable

Answer 1

In MySQL, use insert on duplicate key update. But first you have to define the criteria for recognizing duplicates. For that, you need a unique index (or constraint):

create unique index unq_mytable_profileId on mytable(profileid);

Then:

insert into mytable (profileid, b, c)
    select tt.profileid, tt.b, tt.c
    from temp_table tt
    on duplicate key update b = values(b), c = values(c);