How do I insert rows in Mysql if row doesn't exist with same value

Question

In mySql how do I insert a row if no row exists for a particular email, I need to create a thousand missing rows from a database table, I have a file of five thousand emails I dont know which of the email is already in the table and which is not.

I tried to construct an sql statement for each row like this but it is invalid sql syntax

insert into License (email) select unique '[email protected]' where not exists (select 1 from License where email='[email protected]');

Email is not the primary key of the table and not neccessarily unique, all I am concerned about is if there is no record with the email address add a record otherwise do not.

Gordon Linoff · Accepted Answer · 2017-03-03 13:38:46Z

2

You can fix this by having a from clause for the where:

insert into License (email)
    select email
    from (select '[email protected]' as email) t
    where not exists (select 1 from License l where l.email = t.email);

However, it makes more sense to do all the inserts in one statement:

insert into License (email)
    select t.email
    from database_table t
    where not exists (select 1 from License l where l.email = t.email);

You can add a limit 1000, if you only want 1000 of them.

answered Mar 3, 2017 at 13:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I insert rows in Mysql if row doesn't exist with same value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related