mysql中upset语句,将UPDATE转换为INSERT INTO ON DUPLICATE KEY UPDATE语句

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?

解决方案

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