0

I did some research and couldn't find any solution and hence posting it here.

We have a dataload job which runs on a daily basis. We have separate DML statements to insert, update etc. We wanted to avoid insert statements to run multiple times.

Is there an option to use merge statement in this scenario to update if the record is present or insert if not present?

Please give me an example if possible as I am quite new to sql statements.

We are using Oracle db2 for dbms

Thanks in advance!

arvind_cool
  • 293
  • 1
  • 13

2 Answers2

0

Use an IF EXISTS statement, like this:

IF EXISTS (SELECT 1 FROM #mytable# WHERE #column1# = #this# AND #column2# = #that)
BEGIN
   #update statement#
END

ELSE
BEGIN
    #insert statement#
END

EDIT: the syntax for this can be found here: https://www.toadworld.com/platforms/ibmdb2/w/wiki/7777.ifthenelse

this means that my general case would become this in DB2:

IF (#count of value# = 1) THEN
   #updatestatement#;
ELSEIF (#count of value# > 1) THEN
   #throw an error, as you cannot update uniquely#
ELSE
   #insertstatement#;
END IF;

I also noticed that you can run SQL commands on DB2, which might be useful for this task as well

finally, have a look here, might give you further ideas :)

DB2 for IBM iSeries: IF EXISTS statement syntax

Martin
  • 63
  • 7
0

Got it done throught Merge statements itself :)

MERGE INTO table1(storeent_id, name, value) AS t1
  USING (VALUES ((SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER='test'), 'SOLR_SERVICEABILITY_URL', 
                                                'Sample')) AS t2(storeent_id, name, value)
  ON t1.name = t2.name AND t1.storeent_id = t2.storeent_id
  WHEN MATCHED THEN
     UPDATE SET
        t1.value = t2.value
  WHEN NOT MATCHED THEN
     INSERT
        (name, storeent_id, value)
        VALUES (t2.name, t2.storeent_id, t2.value);
arvind_cool
  • 293
  • 1
  • 13