1

I'm trying to construct a single query that will update multiple rows in a table, using the method described here: Multiple Updates in MySQL

Essentially, I'm using the INSERT ... ON DUPLICATE KEY UPDATE method, using a query like 

INSERT INTO table1 
(id, val1, val2) 
VALUES 
(0, 5, 5)...<etc.> 
ON DUPLICATE KEY 
    UPDATE id=VALUES(id), val1=VALUES(val1), val2=VALUES(val2)

The problem is that the id column is referenced as a foreign key from a different table, so when I try to execute this query, I get a Cannot delete or update a parent row: a foreign key constraint fails error.

Is there a way to make the INSERT DUPLICATE KEY UPDATE method work with this foreign key constraint (I'd rather not disable the constraint, even temporarily), or am I stuck sending multiple queries to my database?

Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40
mt_xing
  • 631
  • 7
  • 22
  • 2
    Just don't update the foreign key? `INSERT INTO table1 (id, val1, val2) VALUES (0, 5, 5)... ON DUPLICATE KEY UPDATE val1=VALUES(val1), val2=VALUES(val2)` – Bart Friederichs Jan 12 '19 at 23:34
  • ON DUPLICATE KEY doesn't fire on a foreign key constraint, so it won't help in this case. Edit your question and show the schemas for the tables involved. So a child table references the ID column? Or visa-versa? Based on the limited info in the question, you may want to add a row for "Not Assigned" the the table causing the constraint to fail. When inserting into this table, use the key for that row. – Sloan Thrasher Jan 13 '19 at 00:21
  • 1
    Either disable the constraint or fix the data so the constraint is valid. You can't have it both ways. – Gordon Linoff Jan 13 '19 at 01:11

0 Answers0