IBM dashDB sql statement for "insert row if not already existing"

Question

I am currently working with the IBM dashDB and I need to know the sql statement for inserting a new row if this row does not already exist based to certain criteria. So, something like this:

INSERT INTO tablexyz (Col1, Col2, Col3) VALUES (val1, val2, val3) IF NOT EXIST (SELECT * FROM tablexyz WHERE val1 = x, val2 = y)

How can I do this?

score 1 · Answer 1 · answered Oct 28 '16 at 22:46

Depending on the context you could define a primary key or unique index on (Col1,Col2) and let the plain Insert fail if there is a duplicate. Or define a procedure that runs the Select and checks the return code. However, the closest match to your SQL example would be a MERGE statement like

MERGE into tablexyz
using ( values (1,2,9) ) newdata(val1,val2,val3)
on tablexyz.Col1 = newdata.val1 and tablexyz.Col2 = newdata.val2
when not matched then insert values(val1,val2,val3);

IBM dashDB sql statement for "insert row if not already existing"

1 Answers1