将UPDATE转换为INSERT INTO ON DUPLICATE KEY UPDATE语句 [英] Convert UPDATE into INSERT INTO ON DUPLICATE KEY UPDATE statement

问题描述

我有这个运行正常的UPDATE MySQL语句.

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

我想转换此UPDATE语句，以便在找不到具有相同table1_nick_no的行时可以添加新行.我相信使用INSERT INTO ON DUPLICATE KEY UPDATE是正确的方法.但是，我尝试了很长时间却失败了. 添加内部联接和where子句插入到重复的密钥更新中

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

如何将这个UPDATE语句转换为相应的INSERT INTO ON DUPLICATE KEY UPDATE语句?

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

推荐答案

您正在寻找的是MERGE或UPSERT(UPDATE/INSERT的缩写).在mySQL中，您可以实现以下链接中所述的UPSET

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/

这篇关于将UPDATE转换为INSERT INTO ON DUPLICATE KEY UPDATE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！