13

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?

Vicky
  • 16,679
  • 54
  • 139
  • 232

2 Answers2

19
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)
Derek
  • 21,828
  • 7
  • 53
  • 61
  • Thanks for the reply. I added a new scenario where I believe above answers will not work! – Vicky Aug 22 '11 at 12:20
  • 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 ??? – Vicky Aug 22 '11 at 12:27
  • 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) – Derek Aug 22 '11 at 12:30
  • 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 – Vicky Aug 22 '11 at 12:41
  • Do you have closing parenthesis on your not exists select? – Derek Aug 22 '11 at 13:05
  • @Nikunj - You need to correlate your `EXISTS` subquery - right now it doesn't relate to the outer query at all. – JNK Aug 22 '11 at 13:05
  • 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: "". SQLSTATE=42601 – Vicky Aug 22 '11 at 13:07
  • 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') – Derek Aug 22 '11 at 13:08
  • @JNK: Can you please reply to the question showing me how. Thanks!! – Vicky Aug 22 '11 at 13:08
  • @Derek Kromm: Are you able to run it with insert query similar to how I am trying ? – Vicky Aug 22 '11 at 13:11
  • @Nikunj: Use `INSERT INTO ... select 'C','3','MY' **FROM dual** where not exists (select ...)` – ypercubeᵀᴹ Aug 22 '11 at 13:30
  • @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. – Vicky Aug 23 '11 at 08:17
  • @Nikunj: `FROM dual`, not `FROM mySchema.DUAL` – ypercubeᵀᴹ Aug 23 '11 at 08:18
  • 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. – ypercubeᵀᴹ Aug 23 '11 at 08:20
  • And this answer suggests it's actually **`SYSIBM.DUAL`** : http://stackoverflow.com/questions/6951713/converting-a-db2-query-into-oracle-query – ypercubeᵀᴹ Aug 23 '11 at 08:26
  • While this answer suggests there's a **`SYSIBM.SYSDUMMY1`**: http://stackoverflow.com/questions/4733356/how-to-test-the-connection-to-a-db2-database – ypercubeᵀᴹ Aug 23 '11 at 08:31
0
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.

Vicky
  • 16,679
  • 54
  • 139
  • 232