如果行不存在，则 Sql 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21110532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql insert if row does not exist
提问by Dimitri
I have 3 tables called table1table2and table3. the table3contains records that have table1.idand table2.idand 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.
我有 3 个表称为table1table2和table3。在table3包含具有记录table1.id和table2.id等一些列也是如此。所以我需要做以下事情。对于表 1 中的每条记录，我需要查看 table3 中是否有一行包含该 table1.id 和任何其他 table2.id，如果没有这样的记录我想插入它。
so here is the example.
所以这里是例子。
suppose table1
假设表1
1 ... ... ...
2 ... ... ...
table2
表2
10 .. .. ..
20 .. .. ..
table3
表3
1 | 10 .. .. ..
2 | 20 .. .. ..
I need to add
我需要添加
1 20 .. .. ..and
2 10 .. .. ..rows to the table3 because for table1.id1 it did not have the row which had all table2.ids (in this case 20) and for table1.id2 it also did not have the row which had all table2.ids (in this case 10) in it. any help would be appreciated
1 20 .. .. ..和
2 10 .. .. ..行到 table3，因为对于table1.id1，它没有包含所有table2.ids（在本例中为 20）table1.id的行，而对于2，它也没有包含所有table2.ids（在本例中为 10）的行。任何帮助，将不胜感激
回答by valex
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)
回答by Ajay
Try this:
尝试这个：
IF NOT EXISTS(SELECT 1 FROM Table3 WHERE Table3.Table1_ID = Table1.ID AND Table3.Table2_ID = Table2.ID)
INSERT INTO Table3(Table1_ID, Table2_ID) VALUES (ID1,ID2)
END IF
回答by Jose Rui Santos
You can also make a cross joinand them insert the combinations that do not exist from that cross join.
您还可以创建 across join并且它们插入该交叉连接中不存在的组合。
insert into table3(col1, col2)
select t.a, t.b
from table3
right join (select table1.col as a, table2.col as b
from table1
cross join table2) t on t.a = table3.col1 and t.b = table3.col2
where table3.col1 is null
and table3.col2 is null;
回答by Nico
Another syntax would be:
另一种语法是：
INSERT INTO t3 (t1id, t2id )
SELECT
t1.id
, t2.id
FROM
t1,t2
EXCEPT
SELECT t1id, t2id from t3
And furthermore you could add triggers on t1 and t2 to fulfill the task automatically.
此外，您可以在 t1 和 t2 上添加触发器以自动完成任务。
