CODEWITHSUNDEEP
mysqlsqlsql-insertcreate-table
Leo Jiang
Leo Jiang

Reputation: 26145

MySQL insert ignore: is it possible to tell which insert failed?

E.g. if I have:

insert ignore into users (email)
values ('[email protected]'), ('[email protected]')

If email is unique and there's already a row with [email protected], then only 1 row is inserted. Is it possible to tell which of the 2 rows was inserted?

As far as I know, insertId refers to the first row that was inserted, but I can't tell which one it was.

Upvotes: 1

Views: 829

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

This answers the opposite problem, which values were not inserted. I'm not sure if that is of interest.

But if so, you can use this trick with on duplicate key update:

insert into users (email)
    values ('[email protected]'), ('[email protected]')
    on duplicate key update email = (case when @emails := concat_ws(',', @emails, values(email)) then values(email) else values(email) end);
    
select @emails;

Here is a db<>fiddle.

Upvotes: 0

GMB
GMB

Reputation: 222582

In MySQL, one option would be to have another column in the table, that keeps track of the last date when each row was updated:

create table users (
    id int auto_increment primary key, 
    email varchar(50) unique,
    updated_at timestamp default current_timestamp on update current_timestamp  
);

Now, say that the content of the table is:

id | email              | updated_at         
-: | :----------------- | :------------------
 1 | [email protected] | 2020-09-23 00:00:00

You run the insert statement, which attempts to insert one duplicate:

insert ignore into users (email)
values ('[email protected]'), ('[email protected]')

You can use updated_at to identify which row was inserted:

id | email                    | updated_at         
-: | :----------------------- | :------------------
 1 | [email protected]       | 2020-09-23 00:00:00
 2 | [email protected] | 2020-09-23 22:33:42

Demo on DB Fiddle

Upvotes: 1

Related Questions