CODEWITHSUNDEEP
sqldatabasedb2insert-updateupsert
Mikael Eriksson
Mikael Eriksson

Reputation: 493

Does DB2 have an "insert or update" statement?

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.

Upvotes: 44

Views: 74186

Answers (5)

Felipe
Felipe

Reputation: 39

Another way is to execute this 2 queries. It's simpler than create a MERGE statement:

update TABLE_NAME set FIELD_NAME=xxxxx where MyID=XXX;

INSERT INTO TABLE_NAME (MyField1,MyField2) values (xxx,xxxxx) 
WHERE NOT EXISTS(select 1 from TABLE_NAME where MyId=xxxx);

The first query just updateS the field you need, if the MyId exists. The second insertS the row into db if MyId does not exist.

The result is that only one of the queries is executed in your db.

Upvotes: 3

akshay
akshay

Reputation: 155

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.

Upvotes: -3

Winston Smith
Winston Smith

Reputation: 21882

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

Upvotes: 44

teknopaul
teknopaul

Reputation: 6772

I found this thread because I really needed a one-liner for DB2 INSERT OR UPDATE.

The following syntax seems to work, without requiring a separate temp table.

It works by using VALUES() to create a table structure . The SELECT * seems surplus IMHO but without it I get syntax errors.

MERGE INTO mytable AS mt USING (
    SELECT * FROM TABLE (
        VALUES 
            (123, 'text')
    )
) AS vt(id, val) ON (mt.id = vt.id)
WHEN MATCHED THEN
    UPDATE SET val = vt.val
WHEN NOT MATCHED THEN
    INSERT (id, val) VALUES (vt.id, vt.val)
;

if you have to insert more than one row, the VALUES part can be repeated without having to duplicate the rest.

VALUES 
    (123, 'text'),
    (456, 'more')

The result is a single statement that can INSERT OR UPDATE one or many rows presumably as an atomic operation.

Upvotes: 19

CupOfTea
CupOfTea

Reputation: 145

This response is to hopefully fully answer the query MrSimpleMind had in use-update-and-insert-in-same-query and to provide a working simple example of the DB2 MERGE statement with a scenario of inserting AND updating in one go (record with ID 2 is updated and record ID 3 inserted).

CREATE TABLE STAGE.TEST_TAB (  ID INTEGER,  DATE DATE,  STATUS VARCHAR(10)  );
COMMIT;

INSERT INTO TEST_TAB VALUES (1, '2013-04-14', NULL), (2, '2013-04-15', NULL); COMMIT;

MERGE INTO TEST_TAB T USING (
  SELECT
    3 NEW_ID,
    CURRENT_DATE NEW_DATE,
    'NEW' NEW_STATUS
  FROM
    SYSIBM.DUAL
UNION ALL
  SELECT
    2 NEW_ID,
    NULL NEW_DATE,
    'OLD' NEW_STATUS
  FROM
    SYSIBM.DUAL 
) AS S
  ON
    S.NEW_ID = T.ID
  WHEN MATCHED THEN
    UPDATE SET
      (T.STATUS) = (S.NEW_STATUS)
  WHEN NOT MATCHED THEN
    INSERT
    (T.ID, T.DATE, T.STATUS) VALUES (S.NEW_ID, S.NEW_DATE, S.NEW_STATUS);
COMMIT;

Upvotes: 11

Related Questions