使用帶有條件的 DB2 合併進行匹配？

在 PHP 腳本中，我正在執行一個工作合併，但我需要向它添加日期條件，並且我的更新導致它不再工作。

我正在尋找的邏輯是這樣的：

1. 如果匹配並且 expire_date 在今天之前，請插入
2. 如果匹配並且 expire_date 在今天之後，請進行更新
3. 如果不匹配，只需插入

匹配一直有效，但我基本上想忽略過期的匹配。我在這裡做錯了什麼？

這是在 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 > 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 < 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)

說明書上說 WHEN MATCHED不允許INSERT。但是，您應該能夠在子句中使用expire_date條件：USING

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 > current_date
WHEN MATCHED 
THEN UPDATE ...
WHEN NOT MATCHED
THEN INSERT ...

引用自：https://dba.stackexchange.com/questions/228007