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

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)