Does DB2 have an "insert or update" statement?

Mikael_Eriksson_40136 0
Viewed 66006

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 issues 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 always do the insert and catch "SQL-code -803 primary key already exists", but I would like to avoid that if possible.

db2 upsert insert-update sql database
5 Answers

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

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en

Joe_350860

I started with hibernate project where hibernate allows you to saveOrUpdate(). I converted that project into JDBC project the problem was with save and update. I wanted to save and update at the same time using JDBC. So, I did some research and I came accross ON DUPLICATE KEY UPDATE :

String sql="Insert into tblstudent (firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

The issue with the above code was that it updated primary key twice which is true as per mysql documentation: The affected rows is just a return code. 1 row means you inserted, 2 means you updated, 0 means nothing happend.

I introduced id and increment it to 1. Now I was incrementing the value of id and not mysql.

String sql="Insert into tblstudent (id,firstName,lastName,gender) values (?,?,?) 
ON DUPLICATE KEY UPDATE 
id=id+1,
firstName= VALUES(firstName),
lastName= VALUES(lastName),
gender= VALUES(gender)";

The above code worked for me for both insert and update.

Hope it works for you as well.

user_92475960
Related