将内部联接和where子句添加到INSERT INTO ON DUPLICATE KEY UPDATE [英] Adding Inner join and where clause to INSERT INTO ON DUPLICATE KEY UPDATE

问题描述

我从此INSERT INTO ON DUPLICATE KEY UPDATE MySQL语句开始.

I started with this INSERT INTO ON DUPLICATE KEY UPDATE MySQL statement.

INSERT INTO Table1 ( field1, field2)
VALUES (1, 2)
ON DUPLICATE KEY UPDATE field1 = 1, field2 = 2

然后，我遇到一个错误外键约束失败".

Then, I encountered an error " a foreign key constraint fails".

我意识到我需要添加另一个WHERE子句条件来满足具有内部联接的外键约束.

I realize I needed to add another WHERE clause condition to satisfy the foreign key constraint with an inner join.

我尝试过类似的事情

INSERT INTO Table1 ( field1, field2)
Inner Join Table2
ON Table2.id = Table1.field_id
VALUES (1, 2)
ON DUPLICATE KEY UPDATE field1 = 1, field2 = 2
WHERE Table2.addr='123456'

我收到语法错误.编写此MySQL语句的正确方法是什么?

I get syntax error. What is the proper way to write this MySQL statement?

推荐答案

由于遇到外键约束，因此必须进行寻址才能使INSERT正常工作.

Since you encountered a foreign key constraint, then you have to address in order for your INSERT to work.

您的查询应类似于:

INSERT INTO Table1 ( field1, field2, foreign_key_id)
Select (1, 2, foreign_key_id)
where 
...
ON DUPLICATE KEY UPDATE field1 = 1, field2 = 2

我相信阅读有关该问题的答案可以解决您的问题. Mysql:如何插入值在具有外键的表中

I believe reading the answers on this question will solve your problem. Mysql: How to insert values in a table which has a foreign key

这篇关于将内部联接和where子句添加到INSERT INTO ON DUPLICATE KEY UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！