Convert UPDATE into INSERT INTO ON DUPLICATE KEY UPDATE statement

Question

I have this UPDATE MySQL statement which works fine.

UPDATE table1 
Inner Join table2 
ON table2.id = table1.gw_id 
SET table1.field1=1, table1.field2=2
WHERE table1.nick_no=4 AND table2.addr=123

I would like to convert this UPDATE statement such that it can add a new row if a row with the same table1_nick_no is not found. I believe using INSERT INTO ON DUPLICATE KEY UPDATE is the right way to go. However, I tried for a long time but failed. Adding Inner join and where clause to INSERT INTO ON DUPLICATE KEY UPDATE

How should I convert this UPDATE statement into the corresponding INSERT INTO ON DUPLICATE KEY UPDATE statement?

Edit the query in your question to use table aliases to we can tell where the columns come from. — Gordon Linoff
Gordon Linoff, I have edited the query to show the tables where the columns belong. — user6064424

mcha mcha · Accepted Answer · 2016-07-05T11:24:01

What you are looking for is a MERGE or an UPSERT (short for UPDATE/INSERT). In mySQL you can achieve an UPSET as described in the following link

http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/

Convert UPDATE into INSERT INTO ON DUPLICATE KEY UPDATE statement

1 Answers