DB2 SQL Statement for Inserting new data and updating existing data

Question

I've found a lot of near misses on this question. So many similar, but not quite right, scenarios. No doubt my ignorance will shine here.

Using DB2 and a shred of knowledge, my scenario is as follows:

On a table, insert a row of data if a given value is not present in a given column, or update the corresponding row if the value is present.

I have a table

id, bigint, not nullable
ref,varchar, nullable

I am not sure if a MERGE is the correct path here as most examples and thorough discussions all seem to revolve around merging one table into another. I'm simply gathering user input and either adding it or updating it. It seems like it should be really simple.

I'm using jdbc and prepared statements to get this done.

Is MERGE the correct way to do this?

When testing my query in DB2 Control Center, I run up against

"No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table"

or a variety of other errors depending on how I structure my MERGE. Here's what I have presently.

merge into table1 as t1
using (select id from table1 group by id) as t2
on t1.id = t2.id
when matched then update set t1.ref = 'abc'
when not matched then insert (t1.id, t1.ref) values (123, 'abc');

If I were to instead compose an update followed by an insert; for new data the insert runs and the update fails, and for existing data they both succeed resulting in bad data in the table, e.g. two identical rows.

The desired result is if on initial use with the values:

id = 1
ref = a

a new row is added. On subsequent use if the values change to:

id = 1
ref = b

the row with id = 1 is updated. Subsequent uses would follow the same rules.

Please let me know how I can phrase this question better.

Update id is not an automatic incrementing key. It's an external key that will be unique but not every thing we are referencing will need a related row in the table I'm attempting to update. This table is rather unstructured on its own but is part of a larger data model.

Answer 1

I'm a bit puzzled by your query. Reading the text makes me suspect that you want something like this:

merge into table1 as t1
using ( values (123, 'abc') ) as t2 (id, ref)
    on t1.id = t2.id
when matched then update 
    set t1.ref = t2.ref
when not matched then 
    insert (id, ref) values (t2.id, t2.ref);

Is that correct?