How to use NOT EXISTS with COMPOSITE KEYS in SQL for inserting data from POJO

Question

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?

Opsis

What DBMS are you doing this on? – Ochone

Answer 1

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

or

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)

Archlute

Thanks for the reply. I added a new scenario where I believe above answers will not work! – Opsis

insert into mytable(d1, d2, d) select d1,d2,d where not exists (select 1 from mytable m where javakey1 = m.key1 and javakey2 = m.key2; ------ is this correct ??? – Opsis

yes, assuming d1, d2, and d in your select are values and not columns from a table (i.e. similar in nature to javakey1, javakey2) – Archlute

Sorry. But not working. I created a table with three columns and first two columns as primary key. Inserted some rows. Now running "insert into NIK(DATA1, DATA2, DATA) select 'C','3','MY' where not exists (select 1 from NIK A where 'C' = A.DATA1 and '3' = A.DATA2;" as per your answer is giving syntax error – Opsis

Do you have closing parenthesis on your not exists select? – Archlute

@Nikunj - You need to correlate your EXISTS subquery - right now it doesn't relate to the outer query at all. – Horvitz

Yes. I have the closing parenthesis. Error is: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "'C','3','MY' where not exists" was found following "DATA2, DATA) select". Expected tokens may include: "<space>". SQLSTATE=42601 – Opsis

This query works fine for me on one of my databases: select '1', '2', '3' where not exists (select 1 from account a where a.accountkey = '2') – Archlute

@JNK: Can you please reply to the question showing me how. Thanks!! – Opsis

@Archlute Kromm: Are you able to run it with insert query similar to how I am trying ? – Opsis

@Nikunj: Use INSERT INTO ... select 'C','3','MY' **FROM dual** where not exists (select ...) – Dejecta

@ypercube: insert into mySchema.NIK(DATA1, DATA2, DATA) select 'C','3','MY' FROM mySchema.DUAL where not exists (select 1 from NIK A where 'C' = A.DATA1 and '3' = A.DATA2) -- not working. SQL0204N "EOD.DUAL" is an undefined name. SQLSTATE=42704. – Opsis

@Nikunj: FROM dual, not FROM mySchema.DUAL – Dejecta

I cannot test in DB2 but from what I know, MySQL and SQL-Server allow SELECT without FROM, while Oracle and DB2 have a special Dual table that has exactly one row and can be used for this. – Dejecta

And this answer suggests it's actually SYSIBM.DUAL : #6952213 – Dejecta

While this answer suggests there's a SYSIBM.SYSDUMMY1: #4733856 – Dejecta

Answer 2

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.

Opsis