Lit Aiy Lit Aiy - 4 months ago 33
MySQL Question

Adding Inner join and where clause to INSERT INTO ON DUPLICATE KEY UPDATE

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".

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

I tried something like this;

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'


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

Answer

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

Your query should look something like;

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

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

Comments