AS400 - Using Merge to add a new record to a table and prevent from a duplicate key error

Question

I have created a new table VISNHDR with the following fields (it is not the entire table but this is enough for my question):

 - ORDNO    (VARCHAR 7) - KEY
 - ORDDT    (NUMERIC 7,0) - KEY
 - ORDTM    (NUMERIC 6,0) - KEY
 - CRTDT    (NUMERIC 7,0)
 - CRTTM    (NUMERIC 6,0)

I am trying to INSERT a new record to this table and I am trying to prevent its insertion in case of a duplicate key. I prefer doing it in one query instead of having one query to check if duplication exists and another one to insert the record. As per my research on the net, I tried to use MERGE but with no success.

MERGE INTO visnhdr v1 USING ( SELECT
    ordno,
    orddt,
    ordtm
   FROM
    visnhdr
    WHERE
    ordno = 'M12345'
    AND orddt = 1170101
    AND ordtm = 101010
) AS V2(ordno,orddt,ordtm)
ON (
    v1.ordno = v2.ordno
    AND v1.orddt = v2.orddt
    AND v1.ordtm = v2.ordtm
)
WHEN NOT MATCHED THEN INSERT (
    ordno,
    orddt,
    ordtm,
    crtdt,
    crttm
) VALUES (
    v2.ordno,
    v2.orddt,
    v2.ordtm,
    1170102,
    101011
);

I am getting an error:

Row not found for MERGE.

What is the correct syntax to achieve my issue?

I don't have a corrected query for you, but the problem is that you're trying to use the results from your v2 table, when there ARE no results in that table. Basically as written you've got "Search for record X. If record X doesn't exist, use the contents of record X to create record X." I suspect that in your when not-matched Values section, you need the parameters that you will eventually be passing in instead of the V2.Field entries. — Hellion, Oct 19 '17 at 17:34

ehh · Accepted Answer · 2017-10-26T05:13:08.683

I found the correct syntax:

MERGE INTO VISNHDR V1 USING (
      VALUES ('M12345', 1170101, 101011, 1170103, 101012)) V2 (ORDNO, ORDDT, ORDTM, CRDDT, CRTTM)
      ON V1.ORDNO = V2.ORDNO AND V1.ORDDT = V2.ORDDT AND V1.ORDTM = V2.ORDTM
WHEN NOT MATCHED THEN
      INSERT VALUES(V2.ORDNO, V2.ORDDT, V2.ORDTM, V2.CRDDT, V2.CRTTM)

Comment:

Depending on how you have defined your columns, you may get an error saying:

one or more of the columns omited from the column list was created as not null.

This is the error I got and I needed to add the other fields I have in my table into the query. And this has nothing related to the syntax issue I was facing with in this post.

AS400 - Using Merge to add a new record to a table and prevent from a duplicate key error

1 Answers1