如果行不存在，则插入另一个表中的行 [英] Insert rows if they don't exist into a table from another table

问题描述

我知道如何使用 insert into 将一行值插入到表中.但是，我找不到合适的习语来完成我所拥有的任务.

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;

但这显然替换了不是我想要的行.我应该以某种方式使用 on duplicate 还是我完全错误的方向?

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?

这两列都有一个唯一的代理键列，它只是一个整数值.

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

推荐答案

如果你有一个唯一的键，那么下面的内容会在重复的键上产生一个错误:

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;

以下是避免此错误的三种方法.第一个是insert ignore:

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

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

insert ignore 的问题在于它会忽略所有错误.因此，除了重复的唯一键错误之外，您可能会错过一些重要的事情.

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.

第三种是有join或notexist逻辑:

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.

这篇关于如果行不存在，则插入另一个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！