仅当值不存在时才从SELECT插入表中 [英] INSERT into a table from SELECT only if value doesn't exist

问题描述

仅在 table1时，我要将 table1.id 插入到 table2.t1col 中。 id 在 table2.t1col 中尚不存在。

I want to insert table1.id into table2.t1col only if table1.id doesn't exist in table2.t1col, yet.

我想我必须使用：

insert into table2 name (t1col) value (select id from table1)

但是我只想添加 id 在<$中不存在

but I want to add only if that id doesn't exist in table2 already.

推荐答案

唯一/索引约束保证了值的唯一性。因此，建议这样做。

A unique/index constraint guarantees the uniqueness of values. So, it is recommended.

但是，不幸的是，违反约束会导致整个 insert 失败。因此，您可以执行以下操作：

Unfortunately, though, a constraint violation causes the entire insert to fail. So, you can do:

insert into table2(t1col) 
    select id
    from table1 t1
    where not exists (select 1 from table2 t2 where t2.t1col = t1.id);

您还应该具有唯一的索引/约束来防止将来出现问题。

You should also have a unique index/constraint to prevent problems in the future.

这篇关于仅当值不存在时才从SELECT插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！