Convert UPDATE into INSERT INTO ON DUPLICATE KEY U

问题:

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?

回答1:

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/