如何在MERGE语句中整合INSERT与UPDATE操作并解决语法错误

我一直尝试在MERGE语句中整合INSERT和UPDATE操作，具体规则如下：

• 主键：location_cd、machine_cd、product_cd
• 若现有记录的status为0，输入数据的status为1，则插入一条新记录，并将现有记录的lastest_flag字段值从"1"更新为"0"
• 其他情况仅更新现有记录的status

示例表：tableA #

输入数据：input1 #

输入数据：input2 #

预期输出 #

• 针对input1，执行UPDATE语句
• 针对input2，插入新记录并更新现有记录的lastest_flag

我的尝试代码（无法正常运行） #

1. 定义输入数据 #

DECLARE @input_data TABLE 
(
    location_cd varchar(10) NOT NULL,
    machine_cd varchar(10) NOT NULL,
    product_cd varchar(6) NOT NULL,
    status varchar(1) NOT NULL,
    updated_timestamp varchar(20) NOT NULL
);

INSERT INTO @input_data
VALUES ('0012345678','1234567890','123456','0','2024/06/10 15:40:15'),
       ('0012345678','1234567890','111111','1','2024/06/10 15:40:15')

2. MERGE语句及错误 #

运行以下MERGE语句时出现错误：

Error 156 Incorrect syntax near the keyword 'BEGIN'

MERGE INTO tableA AS Target
USING @input_data AS Source
                  ON Target.location_cd = Source.location_cd
                  AND Target.machine_cd = Source.machine_cd
                  AND Target.product_cd = Source.product_cd

WHEN MATCHED AND Target.status = 1 AND Source.status = 0 AND Target.updated_timestamp < Source.updated_timestamp 
    THEN
        UPDATE SET
            Target.status = Source.status,
            Target.updated_timestamp = Source.updated_timestamp

WHEN MATCHED AND Target.status = 0 AND Source.status = 1 AND Target.updated_timestamp < Source.updated_timestamp 
    THEN
    BEGIN
        INSERT (machine_cd, location_cd, product_cd, status, lastest_flag, updated_timestamp)
        VALUES (Source.machine_cd, Source.location_cd, Source.product_cd, Source.status, '1', Source.updated_timestamp);

        UPDATE 
            SET Target.lastest_flag = '0';
    END;

解决方案 #

SQL的MERGE语句不允许在单个WHEN MATCHED分支中同时执行UPDATE和INSERT操作，每个匹配分支只能执行一种数据修改操作。要实现需求，需要拆分逻辑为两步：

步骤1：更新符合条件的现有记录 #

先处理所有更新逻辑，包括普通状态更新和特殊的lastest_flag更新：

UPDATE t
SET 
    t.status = CASE 
        WHEN t.status = 1 AND s.status = 0 THEN s.status 
        ELSE t.status 
    END,
    t.updated_timestamp = CASE 
        WHEN t.status = 1 AND s.status = 0 THEN s.updated_timestamp 
        ELSE t.updated_timestamp 
    END,
    t.lastest_flag = CASE 
        WHEN t.status = 0 AND s.status = 1 THEN '0' 
        ELSE t.lastest_flag 
    END
FROM tableA t
JOIN @input_data s ON 
    t.location_cd = s.location_cd 
    AND t.machine_cd = s.machine_cd 
    AND t.product_cd = s.product_cd
WHERE t.updated_timestamp < s.updated_timestamp;

步骤2：插入新记录 #

再插入符合条件的新记录（现有status为0且输入status为1的场景）：

INSERT INTO tableA (location_cd, machine_cd, product_cd, status, lastest_flag, updated_timestamp)
SELECT 
    s.location_cd, 
    s.machine_cd, 
    s.product_cd, 
    s.status, 
    '1', 
    s.updated_timestamp
FROM @input_data s
JOIN tableA t ON 
    t.location_cd = s.location_cd 
    AND t.machine_cd = s.machine_cd 
    AND t.product_cd = s.product_cd
WHERE 
    t.status = 0 
    AND s.status = 1 
    AND t.updated_timestamp < s.updated_timestamp;

完整可执行代码 #

将两步逻辑整合后的完整代码：

DECLARE @input_data TABLE 
(
    location_cd varchar(10) NOT NULL,
    machine_cd varchar(10) NOT NULL,
    product_cd varchar(6) NOT NULL,
    status varchar(1) NOT NULL,
    updated_timestamp varchar(20) NOT NULL
);

INSERT INTO @input_data
VALUES ('0012345678','1234567890','123456','0','2024/06/10 15:40:15'),
       ('0012345678','1234567890','111111','1','2024/06/10 15:40:15');

-- 更新现有记录
UPDATE t
SET 
    t.status = CASE 
        WHEN t.status = 1 AND s.status = 0 THEN s.status 
        ELSE t.status 
    END,
    t.updated_timestamp = CASE 
        WHEN t.status = 1 AND s.status = 0 THEN s.updated_timestamp 
        ELSE t.updated_timestamp 
    END,
    t.lastest_flag = CASE 
        WHEN t.status = 0 AND s.status = 1 THEN '0' 
        ELSE t.lastest_flag 
    END
FROM tableA t
JOIN @input_data s ON 
    t.location_cd = s.location_cd 
    AND t.machine_cd = s.machine_cd 
    AND t.product_cd = s.product_cd
WHERE t.updated_timestamp < s.updated_timestamp;

-- 插入新记录
INSERT INTO tableA (location_cd, machine_cd, product_cd, status, lastest_flag, updated_timestamp)
SELECT 
    s.location_cd, 
    s.machine_cd, 
    s.product_cd, 
    s.status, 
    '1', 
    s.updated_timestamp
FROM @input_data s
JOIN tableA t ON 
    t.location_cd = s.location_cd 
    AND t.machine_cd = s.machine_cd 
    AND t.product_cd = s.product_cd
WHERE 
    t.status = 0 
    AND s.status = 1 
    AND t.updated_timestamp < s.updated_timestamp;

内容的提问来源于stack exchange，提问作者ninh_nguyen