我正在使用DB2 DBMS.
场景1：
myTable有一个复合键(key1,key2),其中key1和key2都是来自yourTable的外键.
我想将yourTable中的新数据插入到myTable中,但前提是myTable中还没有key1,key2组合.
insert into myTable(key1,key2,someData) values(x,y,z) where NOT EXISTS (want to check if composite key is not already present)
场景2：
我使用属性data1,data2和data将数据放入yourTable的java对象中.
我想在Scenario1中插入带有检查的上述数据. data1 data2不应该已经出现在myTable中.
我该如何实现这一目标？我认为我们不能在insert语句中使用SELECT语句.
insert into myTable(key1,data) values(data1,data2,data) where (data1 + data2 are already not present in myTable)
我怎样才能做到这一点？
解决方法
insert into mytable(...) select ... from yourtable y left join mytable m on y.key1 = m.key1 and y.key2 = m.key2 where m.key is null
要么
insert into mytable(...) select ... from yourtable y where not exists (select 1 from mytable m where y.key1 = m.key1 and y.key2 = m.key2)
对于你的第二个场景,它看起来与上面的查询类似
insert into mytable(...) select ... where not exists (select 1 from mytable m where javakey1 = m.key1 and javakey2 = m.key2)
