Does DB2 have an "insert or update" statement?

Question

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed.

My code now does a select and if no result is returned it does an insert. I really don't like this code since it exposes me to concurrency issuses when running in a multi-threaded environment.

What I would like to do is to put this logic in DB2 instead of in my Java code. Does DB2 have an "insert-or-update" statement? Or anything like it that I can use?

For example:

insertupdate into mytable values ('myid')

Another way of doing it would probably be to allways do the insert and catch "SQL-code -803 primary key already exists", but I would like to avoid that if possible.

Answer 1

Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert).

MERGE INTO target_table USING source_table ON match-condition
{WHEN [NOT] MATCHED 
          THEN [UPDATE SET ...|DELETE|INSERT VALUES ....|SIGNAL ...]}
[ELSE IGNORE]

See:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm

Answer 2

Please also refer to the thread Trying to avoid -803 in DB2 insert in this forum.