Using a DB2 merge with conditionals for matches? Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work.

The logic I'm looking for is this:


If there's a match AND the expire_date is before today, do an insert
If there's a match AND the expire_date is after today, do an update
If no match, just insert


The matching has been working but I basically want to disregard matches that are expired. What am I doing wrong here?

This is running in db2 for iseries

                    MERGE INTO products AS P
                    USING ( VALUES (
                        CAST(:CUSTOMER AS INT),
                        CAST(:SLSCODE AS INT),
                        CAST(:DTL1 AS INT),
                        CAST(:DTL2 AS INT),
                        CAST(:DTL3 AS INT),
                        CAST(:COUNT AS INT),
                        CAST(:LAST_DATE AS DATE),
                        CAST(:FLAG AS SMALLINT),
                        CAST(:ORDER AS INT),
                        CAST(:PIECES AS VARCHAR(45))
                        )
                    )
                AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
                ON s.CUSTOMER = p.CUSTOMER and s.REGION = p.REGION and s.DTL1 = p.DTL1 and s.DTL2 = p.DTL2 and s.DTL3 = p.DTL3 and s.COUNT = p.COUNT and s.LAST_DATE = p.LAST_DATE and s.FLAG = p.FLAG and s.ORDER = p.ORDER AND s.PIECES = p.PIECES


                WHEN MATCHED AND p.expire_date &gt; current_date
                    THEN UPDATE SET  last_date = s.last_date, order = s.order, pieces = s.pieces, expire_date = s.expire_date

                WHEN MATCHED AND p.expire_date &lt; current_date
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

                WHEN NOT MATCHED
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)
The manual says WHEN MATCHED does not allow INSERT. However, you should be able to use the expire_date condition in the USING clause:

MERGE INTO products AS P
USING ( VALUES (
...
    )
)
AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
ON s.CUSTOMER = p.CUSTOMER 
and s.REGION = p.REGION 
and ...
and p.expire_date &gt; current_date
WHEN MATCHED 
THEN UPDATE ...
WHEN NOT MATCHED
THEN INSERT ...