db2 Upsert(merge)用于更新记录(如果存在),否则插入

vaqhlq81  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(206)

我正在尝试编写一个DB2查询,它允许我更新已经存在的记录,但如果不存在,则应该插入该记录。我编写了以下查询,它应该可以完成此操作:

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)

此查询应尝试检查是否已存在符合选择标准的记录。更新记录似乎工作正常,但我无法使“WHEN NOT MATCHED”部分工作并插入新记录。有人能提供一些帮助吗?
该表用于保存某个软件包的激活日期。PACKAGE是对包含该软件包名称的软件包表的引用(例如“M2108”)。CALENDAR_ID是指该软件包将被激活的系统。实际日期存储在ACT_DATE中。
无法将DDL导入SQLFiddle,因此我必须在此处提供:

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);
mepcadol

mepcadol1#

如果您的目标是将ACT_DATE设置为31.12.2021,如果找到PACKAGE = M2108且CALENDAR_ID = 13的行,并且没有找到具有这些值的行,则插入该行,则答案可能是这样

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)

相关问题