INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE

[Marius]

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

• ON DUPLICATE KEY UPDATE - which implies an unnecessary update at some cost.
  INSERT IGNORE - which implies an invitation for other kinds of failure to slip in unannounced.

What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

Hi,
If you just want to skip rows with duplicate values, and no update needed, I think that is proper to use INSERT IGNORE.
If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error.

INSERT ... ON DUPLICATE KEY UPDATE is indicated in case you want to cange data when rezults in a duplicate key.

Example:

Code: Select all

/* If you want to skip if there is duplicate id */
INSERT IGNORE INTO table (id, col) VALUES (1, 1);

/* Update col data if there is duplicate id */
INSERT INTO table (id, col) VALUES (1, 1) ON DUPLICATE KEY UPDATE col = 1