如何在 SQL 中使用 NOT EXISTS 和 COMPOSITE KEYS 从 POJO 插入数据 [英] How to use NOT EXISTS with COMPOSITE KEYS in SQL for inserting data from POJO

问题描述

我正在使用 DB2 DBMS.

I am using DB2 DBMS.

场景 1:

myTable 有一个复合键 (key1, key2)，其中 key1 和 key2 都是 yourTable 的外键.

myTable has a composite key (key1, key2) where both key1 and key2 are foreign keys from yourTable.

我想将 yourTable 中的新数据插入 myTable，但前提是 myTable 中不存在 key1、key2 组合.

I want to insert new data from yourTable into myTable, but only if the key1, key2 combination does not already exist in myTable.

insert into myTable(key1, key2, someData)
values(x, y, z)
where NOT EXISTS (want to check if composite key is not already present)

场景 2:

我将数据从 yourTable 放入一个带有 data1、data2 和 data 属性的 java 对象中.

I put data into a java object from yourTable with properties data1, data2, and data.

我想插入上面的数据与场景1中的检查一样.data1 + data2 不应已存在于 myTable 中.

I want to insert the above data with the check as in Scenario1. data1 + data2 should not already be present in myTable.

我如何实现这一目标?我认为我们不能在插入语句中使用 SELECT 语句.

How do I achieve this? I don't think we can use a SELECT statement inside the insert statement.

insert into myTable(key1, key2, data)
values(data1, data2, data)
where (data1 + data2 are already not present in myTable)

我怎样才能做到这一点?

How can I achieve this?

推荐答案

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)

对于您的第二种情况，它看起来类似于上面的查询

for your 2nd scenario, it'd look similar to the above query

insert into mytable(...)
select ...
where not exists (select 1 from mytable m where javakey1 = m.key1 and javakey2 = m.key2)

这篇关于如何在 SQL 中使用 NOT EXISTS 和 COMPOSITE KEYS 从 POJO 插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！