DB2 MERGE statement error

Question

I have tried the following with a couple of variations but I continue to get errors. Any way to get this fixed. DB2 10.1 (DB2 for z/OS V10)

For the following

MERGE INTO TRGT t
USING SRC s
ON (t.ACCTID=s.ACCTID AND s.SEQID=123)
WHEN MATCHED THEN
UPDATE SET
MyFlag = 'Y'

Error: An unexpected token "SRC" was found following "". Expected tokens may include: "(". SQLSTATE=42601

SQLState: 42601 ErrorCode: -104

---

However for the following

MERGE INTO TRGT t
USING (SELECT SEQID, ACCTID FROM SRC WHERE SEQID=123) s
ON (t.ACCTID=s.ACCTID)
WHEN MATCHED THEN
UPDATE SET
MyFlag = 'Y'

Error: The use of the reserved word "SELECT" following "" is not valid. Expected tokens may include: "VALUES". SQLSTATE=42601

SQLState: 42601 ErrorCode: -199

Answer 1

Unfortunately (and I never understood why), in DB2 for z/OS, you can't use a table as a source for the MERGE, you can only use a VALUES clause. Additionally, if you want to merge multiple rows, you have to use host variable arrays, and specify the number of values are in your array.

MERGE INTO TRGT t
USING (VALUES (:param1, :param2) FOR :paramNumRows) s
   ON (t.ACCTID=s.ACCTID)
 WHEN MATCHED THEN
     UPDATE SET MyFlag = 'Y'