CODEWITHSUNDEEP
mysqlsql
Will
Will

Reputation: 75665

Attempt multiple inserts and get list of IDs that failed

In MySQL, can you insert multiple rows into a table and get a list back of the rows that succeeded or failed?

Or must you insert each row individually in its own statement in order to determine if the insert succeeded?

(Can multiple inserts be submitted in a batch but the success of each individually accounted for in any other DB? E.g. PostgreSQL?)

Upvotes: 2

Views: 3487

Answers (1)

sjdaws
sjdaws

Reputation: 3536

There is no way to find out which rows failed when doing a bulk insert. Usually a query will fail if you are doing multiple inserts and one of them fails for any reason.

However, there are a few options you can take to ensure your query succeeds.

1) Use REPLACE INTO rather than INSERT INTO. This will cause MySQL to over write the existing row if the primary key already exists.

2) Use INSERT IGNORE INTO to skip over existing rows. The downside is you won't know which rows have been skipped.

3) Use INSERT INTO ... ON DUPLICATE KEY UPDATE .... This will allow you to update one, two, or all fields of the row which already exists. You can also probably use this as a pseudo failure indicator if you have an empty field.

// clear out failures from previous runs
UPDATE table SET failures = 0;

// insert the row if we can, otherwise record the failure and move on without updating the row
INSERT INTO table
    (id, name) VALUES 
    (1, 'John'),
    (2, 'Steve'),
    ...
    (500, 'Tim')
    ON DUPLICATE KEY UPDATE failures = failures + 1;

// check failures
SELECT id, name, failures FROM table;

Upvotes: 8

Related Questions