Both “INSERT IGNORE” and “… ON DUPLICATE KEY UPDATE” what happens?

Question

What will mysql do with both INSERT IGNORE and … ON DUPLICATE KEY UPDATE ?

This is not a question about their differences. I ask because Talend ETL does this behind the UI and I'm worried it will have side effects especially if I don't want to update and do something like this:

String insertIgnore_tMysqlOutput_10 = "INSERT IGNORE INTO `"
+ "Employees"
+ "` (`Name`,`JobTitle`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Name` = ?";

score 3 · Accepted Answer · answered Jun 05 '12 at 02:03

3

IGNORE just acts as a kind of error-suppressor, making fatal errors act as warnings instead.

ON DUPLICATE KEY UPDATE doesn't trigger an error, so INGORE has no effect on it.

Therefore, IGNORE has no effect on duplicate keys when ON DUPLICATE KEY UPDATE is used as well. However, if a different error were to occur then IGNORE would indeed have an effect.

answered Jun 05 '12 at 02:03

Niet the Dark Absol

320,036
81
464
592

Hmm I think it is an error on the part of Talend ETL to use ignore – KCD Jun 05 '12 at 02:49
One example: inserting NULL into a NOT NULL will insert the column type default and silently continue – KCD Dec 03 '13 at 01:33

Both “INSERT IGNORE” and “… ON DUPLICATE KEY UPDATE” what happens?

1 Answers1