Does DB2 have an “insert or update” statement?


Answers

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.

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 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.




Where not exists causing problems for insert into, db2

This will work:

insert into mySchema.myTable (award_id, cust_id) 
select 'blahblah', 12345
from sysibm.sysdummy1
where not exists (select * from mySchema.myOtherTable where cust_id = 12345);

An alternative to sysibm.sysdummy1 would be:

insert into mySchema.myTable (award_id, cust_id) 
select 'blahblah', 12345
from ( values (1) )
where not exists (select * from mySchema.myOtherTable where cust_id = 12345);



insert into mySchema.myTable (award_id, cust_id) 
(select 'blahblah', 12345 from sysibm.sysdummy1
where not exists (select * from mySchema.myOtherTable where cust_id = 12345));

This ended up working.




+UPDATE+

DB2 for i, as of version 7.1, now has a MERGE statement which does what you are looking for.

>>-MERGE INTO--+-table-name-+--+--------------------+----------->
               '-view-name--'  '-correlation-clause-'   

  >--USING--table-reference--ON--search-condition----------------->

     .------------------------------------------------------------------------.   
     V                                                                        |   
  >----WHEN--+-----+--MATCHED--+----------------+--THEN--+-update-operation-+-+----->
             '-NOT-'           '-AND--condition-'        +-delete-operation-+     
                                                         +-insert-operation-+     
                                                         '-signal-statement-'     

See IBM i 7.1 InfoCenter DB2 MERGE statement reference page




JDBC insert or update practice

It depends on what type of database your are using and whether or not you can take advantage of database specific features. MySQL for instance lets you do the following:

INSERT INTO territories (code, territory) VALUES ('NO', 'Norway')
ON DUPLICATE KEY UPDATE territory = 'Norway'

However, the above is not standard (SQL-92) compliant. That is, it will most likely not work on all databases. In other words, you would have to stick with the code as you have written it. It might not look that elegant, but it is probably the most safe solution to go with.




Maybe the database you are using has an insert or update feature which solves this automatically for you. In DB2 you can use MERGE INTO for example. See here






Tags