如果值不同，则DB2，MERGE INTO更新

amdev 发表于 Dev

阿姆德夫

我有这种工作的DB2请求

MERGE INTO table_to_upsert AS tab
USING (VALUES
        (1, 2, 3),
        (4, 5, 6),
        (7, 8, 9)
        -- more rows
    ) AS merge (C1, C2, C3)
    ON tab.key_to_match = merge.key_to_match -- usually the primary key example : tab.C2 = merge.C2
    WHEN MATCHED THEN
        UPDATE SET tab.C1 = merge.C1,
                   tab.C2 = merge.C2,
                   tab.C3 = merge.C3
    WHEN NOT MATCHED THEN
        INSERT (C1, C2, C3)
        VALUES (merge.C1, merge.C2, merge.C3)

但是现在，我想要的是，如果主键已经存在，然后进行更新，但只有C1的新值与旧值不同时，才更新C1。

理论上是这样的

MERGE INTO table_to_upsert AS tab
USING (VALUES
        (1, 2, 3),
        (4, 5, 6),
        (7, 8, 9)
        -- more rows
    ) AS merge (C1, C2, C3)
    ON tab.key_to_match = merge.key_to_match
    WHEN MATCHED THEN
        UPDATE SET tab.C1 = merge.C1,
                   tab.C2 = merge.C2,
                   tab.C3 = merge.C3
        WHERE tab.C1 != merge.C1
    WHEN NOT MATCHED THEN
        INSERT (C1, C2, C3)
        VALUES (merge.C1, merge.C2, merge.C3)

我添加，WHERE tab.C1 != merge.C1但是它不起作用，导致

错误：非法符号在哪里；有效符号

相对PHP新手

如果我没记错的话，可以在匹配的行中添加一个搜索条件，即

MERGE INTO table_to_upsert AS tab
USING (VALUES
        (1, 2, 3),
        (4, 5, 6),
        (7, 8, 9)
        -- more rows
    ) AS merge (C1, C2, C3)
    ON tab.key_to_match = merge.key_to_match
    WHEN MATCHED AND tab.C1 != merge.C1 THEN
        UPDATE SET tab.C1 = merge.C1,
                   tab.C2 = merge.C2,
                   tab.C3 = merge.C3
    WHEN NOT MATCHED THEN
        INSERT (C1, C2, C3)
        VALUES (merge.C1, merge.C2, merge.C3)