Upsert (merge) for updating record if it exists and inserting otherwise

I am trying to write a DB2 query that allows me to either update a record if it already exists but if it does not exist it should be inserted. I wrote the following query that should accomplish this:

MERGE INTO OA1P.TLZ712A1 AS PC
USING (
    SELECT * FROM OA1P.TLZ712A1 
    WHERE CALENDAR_ID=13 AND 
    "PACKAGE"='M2108'
) PC2
ON (PC.ID_PACKAGE_CALENDAR=PC2.ID_PACKAGE_CALENDAR)
WHEN MATCHED THEN 
    UPDATE SET ACT_DATE = '31.12.2021'
WHEN NOT MATCHED THEN 
    INSERT ("PACKAGE", ACT_DATE, CALENDAR_ID, PREPTA, MIXED) VALUES ('M2108', '31.12.2021', 13, 0, 0)

This query should attempt to check if a record already exists for the selection criteria. Updating a record seems to be working fine but I am not able to get the “WHEN NOT MATCHED” part to work and inserting a new record. Anyone able to provide some assistance?

The table is used to save the activation date of a certain software package. PACKAGE is the reference to the package table containing the name of the package (eg. “M2108”). CALENDAR_ID refers to a system where the software package will be activated. The actual date is stored in ACT_DATE.

Did not manage to get the DDL into SQLFiddle so I have to provide it here:

CREATE TABLE OA1P.TLZ712A1 (
    ID_PACKAGE_CALENDAR INTEGER DEFAULT IDENTITY GENERATED BY DEFAULT NOT NULL,
    CALENDAR_ID INTEGER,
    "PACKAGE" VARCHAR(10) NOT NULL,
    ACT_DATE DATE NOT NULL,
    PREPTA SMALLINT DEFAULT 0 NOT NULL,
    MIXED SMALLINT DEFAULT 0 NOT NULL,
    "COMMENT" VARCHAR(60) NOT NULL,
    LAST_MODIFIED_PID CHAR(7) NOT NULL,
    ST_STARTID TIMESTAMP NOT NULL,
    ST_FROM TIMESTAMP NOT NULL,
    ST_TO TIMESTAMP NOT NULL,
    CONSTRAINT TLZ712A1_PK PRIMARY KEY (ID_PACKAGE_CALENDAR),
    CONSTRAINT CALENDAR FOREIGN KEY (CALENDAR_ID) REFERENCES OA1P.TLZ711A1(ID_CALENDAR) ON DELETE RESTRICT,
    CONSTRAINT "PACKAGE" FOREIGN KEY ("PACKAGE") REFERENCES OA1P.TLZ716A1(NAME) ON DELETE RESTRICT
);
CREATE UNIQUE INDEX ILZ712A0 ON OA1P.TLZ712A1 (ID_PACKAGE_CALENDAR);

Answer

If your goal is to set ACT_DATE to 31.12.2021 if a row is found with PACKAGE = M2108 and CALENDAR_ID = 13 and if no row is found with these values then insert it, then this could be the answer

MERGE INTO OA1P.TLZ712A1 AS PC
USING (
   VALUES ('M2108', 13, date '31.12.2021')
) PC2 ("PACKAGE", CALENDAR_ID, ACT_DATE)
ON (PC."PACKAGE", PC.CALENDAR_ID) = (PC2."PACKAGE", PC2.CALENDAR_ID)
WHEN MATCHED THEN 
    UPDATE SET ACT_DATE = PC2.ACT_DATE
WHEN NOT MATCHED THEN 
    INSERT ("PACKAGE", ACT_DATE, CALENDAR_ID, PREPTA, MIXED) VALUES (PC2."PACKAGE", PC2.ACT_DATE, PC2.CALENDAR_ID, 0, 0)