Db2 iseries INSERT ON DUPLICATE KEY

Question

Does anybody knows if DB2 for i (V6r1) supports something like

INSERT ON DUPLICATE KEY UPDATE.

I need to do update with joins but don't want to use subqueries.

Answer 1

In IBM i v7.1 you will have the new MERGE statement

In v6.1, you can UPDATE where a matching row is found, and then another statement to INSERT where there is no match.

For example, an update might look like this:

    UPDATE targetfile as u                                  
      SET  (  flda,fldb,fldc ) =
        (SELECT  a,   b,   c
                FROM inpdata as i           
                WHERE u.keyfld = i.keyfld
             )
      WHERE u.keyfld IN 
             (SELECT i.keyfld
                FROM inpdata as i           
             );

then

    INSERT INTO targetfile 
            (keyfld, flda, fldb, fldc)
      SELECT keyfld, a,    b,    c
        FROM inpdata as i
        WHERE i.keyfld  NOT IN 
             (SELECT t.keyfld
                FROM targetfile as t           
             );

or

    INSERT INTO targetfile 
            (keyfld, flda, fldb, fldc)
      SELECT keyfld, a,    b,    c
        FROM                inpdata    as i
        LEFT EXCEPTION JOIN targetfile as t           
          ON i.keyfld = t.keyfld;

Answer 2

You can solve this by using 'MERGE'.like this:

1.Step One:table 'TEST' has a table structure (A,B,C) with a prime key 'A', existing a data record(1,2,3).

2.Step two:now you can insert a record (1,9,9) by using this SQL command as follows:

MERGE INTO TEST AS T
USING (VALUES( 1, 9,  9))  AS DAT(A, B,  C)
ON T.A = DAT.A 
WHEN MATCHED THEN UPDATE  SET T.C = DAT.C
WHEN NOT MATCHED THEN  INSERT (A, B,  C)  VALUES (DAT.A, DAT.B, DAT.C)