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.

Gordon Linoff · Accepted Answer · 2014-04-22 22:34:10Z

3

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.

answered Apr 22, 2014 at 22:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user3081519 Over a year ago

Thanks, I think the second way is the best for my purposes out of all proposed. Looks good and worked well.

Community · Accepted Answer · 2017-05-23 12:28:35Z

0

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.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 22, 2014 at 22:30

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Comments

Obewan · Accepted Answer · 2024-06-27 12:42:32Z

I think you are looking for something more like the following:

INSERT INTO destination_table 
(field_name)
SELECT t1.field_name
FROM source_table t1
WHERE NOT EXISTS(SELECT field_name
                 FROM destination_table t2
                 WHERE t2.field_name = t1.field_name)

You can insert multiple data fields by modifying the query to something like the following:

INSERT INTO destination_table 
(id_field_name,field_name)
SELECT t1.id_field_name,t1.field_name
FROM source_table t1
WHERE NOT EXISTS(SELECT id_field_name
                 FROM destination_table t2
                 WHERE t2.id_field_name = t1.id_field_name) LIMIT 1;

I've been using the above in various forms for some time on MySQL and MariaDB databases, but your mileage may vary.

PS: The Limit clause provides a way to test your query without totally wrecking your data.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related