Insert rows if they don't exist into a table from another table

Question

I know how to make an insert of a row of values into a table using insert into. However, I cannot find an appropriate idiom for accomplishing the task that I have.

I have two tables. One table is the primary table and the other is an additional table. I need to insert rows that do not exist in the primary from additional table ignoring any duplicates.

I am trying to use some variations of:

replace into primary 
select * from additional;

But this obviously replaces the rows which is not what I want. Should I use on duplicate somehow or am I in a completely wrong direction?

EDIT:

Both of the columns have a unique surrogate key column which is just an integer value.

score 1 · Accepted Answer · answered Apr 22 '14 at 22:34

If you have a unique key, then the following will generate an error on the duplicate keys:

insert into primary(col1, . . .)
    select col1, . . .
    from secondary;

Here are three ways to avoid this error. The first is insert ignore:

insert ignore into primary(col1, . . .)
    select col1, . . .
    from secondary;

The problem with insert ignore is that it ignores all errors. So, you might miss something important other than the duplicate unique key error.

The second is on duplicate key update. You need to put in a "no-op" update statement:

insert into primary(col1, . . .)
    select col1, . . .
    from secondary
    on duplicate key update col1 = col1;

This is often the easiest way.

The third is to have join or not exists logic:

insert into primary(col1, . . .)
    select col1, . . .
    from secondary
    where not exists (select 1 from primary where primary.keycol = secondary.keycol);

I think this can result in race conditions if two queries are inserting rows at the same time.

Thanks, I think the second way is the best for my purposes out of all proposed. Looks good and worked well. — user3081519, Apr 23 '14 at 00:06

score 0 · Answer 2 · edited May 23 '17 at 12:28

You are going in the wrong direction. You want something like this:

insert into primary
(field1, field2, etc)
select distinct field_a, field_b, etc
from additional
where whatever.

The where clause is the spot to exclude existing records. Apparently MySQL does not support the except keyword, so you have to use other methods. This link will help you with that.

Also, if primary has an autogenerated primary key, let the database generate it. Ignore the PK field from additional.

Insert rows if they don't exist into a table from another table

2 Answers2

Related