如果行不存在，则 Sql 插入 [英] Sql insert if row does not exist

问题描述

我有 3 个表，分别称为 table1 table2 和 table3.table3 包含具有 table1.id 和 table2.id 以及其他一些列的记录.所以我需要做以下事情.对于表 1 中的每条记录，我需要查看 table3 中是否有一行包含该 table1.id 和任何其他 table2.id，如果没有这样的记录我想插入它.

I have 3 tables called table1 table2 and table3. the table3 contains records that have table1.id and table2.id and some other columns as well. So I need to do the following. for each record in table 1 I need to see if in table3 there is a row containing that table1.id and any other table2.id if there is no such record I want to insert it.

这里是例子.

假设表1

1 ... ... ...
2 ... ... ...

table2

10 .. .. ..
20 .. .. ..

table3

1 | 10 .. .. ..
2 | 20 .. .. ..

我需要添加

1 20 .. .. .. 和2 10 .. .. 行到 table3 因为对于 table1.id 1 它没有包含所有 table2.ids(在本例中为 20)并且对于 table1.id 2 它也没有包含所有 table2.ids(在本例中为 10)的行.任何帮助将不胜感激

1 20 .. .. .. and 2 10 .. .. .. rows to the table3 because for table1.id 1 it did not have the row which had all table2.ids (in this case 20) and for table1.id 2 it also did not have the row which had all table2.ids (in this case 10) in it. any help would be appreciated

推荐答案

如果我猜对了，试试这个:

If I've got it right try this:

INSERT INTO Table3 (Table1_id,Table2_id)
SELECT Tablei.id,Table2.id FROM Table1,Table2
  WHERE NOT EXISTS (SELECT 1 
                          FROM Table3 
                          WHERE Table3.Table1_id=Table1.ID 
                                AND
                                Table3.Table2_id=Table2.ID)

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