問題描述

我正在使用 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 插入數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！