CODEWITHSUNDEEP
mysqlsqldatabase
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

Which record caused INSERT IGNORE not to insert?

In MySQL, when an INSERT IGNORE does not insert anything, is it possible to get the row that caused INSERT IGNORE to "fail"?

Upvotes: 1

Views: 78

Answers (2)

newtover
newtover

Reputation: 32094

Another and more correct approach is to use LAST_INSERT_ID() in ON DUPLICATE KEY UPDATE

That is to change:

INSERT IGNORE INTO the_table (id) VALUES (1);

To:

INSERT INTO the_table (id) VALUES (1)
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id);

This wil make no changes to the table, but the following call to LAST_INSERT_ID() will return either the id of the newly inserted item or the last result explicitly passed to the function.

Upvotes: 2

pgl
pgl

Reputation: 8011

One way you can achieve this is to add a field which you can use as a flag, and then add ON DUPLICATE KEY UPDATE dup = dup+1 (or similar) to your query.

Upvotes: 0

Related Questions