我正在尝试编写一个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);
1条答案
按热度按时间mepcadol1#
如果您的目标是将ACT_DATE设置为31.12.2021,如果找到PACKAGE = M2108且CALENDAR_ID = 13的行,并且没有找到具有这些值的行,则插入该行,则答案可能是这样