如何在 SQL 中使用 NOT EXISTS 和 COMPOSITE KEYS 从 POJO 插入数据

发布于 2024-11-30 22:14:27 字数 728 浏览 1 评论 0原文

我正在使用 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：

我将 yourTable 中的数据放入具有 data1、data2 和 data 属性的 java 对象中。

我想插入上述数据并进行检查，如场景 1 所示。 data1 + data2 不应已存在于 myTable 中。

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

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

我怎样才能实现这个目标？

原文

I am using DB2 DBMS.

Scenario 1:

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

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)

Scenario 2:

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

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

两相知 2024-12-07 22:14:27

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)

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)

回复收藏 0 原文

风向决定发型 2024-12-07 22:14:27

insert into mySchema.NIK(DATA1, DATA2, DATA) select 'C','3','MY' FROM SYSIBM.DUAL where not exists (select 1 from mySchema.NIK A where 'C' = A.DATA1 and '3' = A.DATA2)

如果数据“c”“3”已存在，则上述查询将导致空表。

insert into mySchema.NIK(DATA1, DATA2, DATA) select 'C','3','MY' FROM SYSIBM.DUAL where not exists (select 1 from mySchema.NIK A where 'C' = A.DATA1 and '3' = A.DATA2)

if the data 'c' '3' already exists then above query will result in an empty table.

回复收藏 0 原文

~没有更多了~