amdev amdev - 3 months ago 28
SQL Question

DB2, MERGE INTO update if value different

I have this kind of DB2 request which work

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)


But now, what I want is, IF primary key already existe, then update, BUT only update C1 if the new value of C1 is different from the old one.

In theory something like that

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)


I add the
WHERE tab.C1 != merge.C1
but it's not working, result in


Error: ILLEGAL SYMBOL WHERE; VALID SYMBOLS

Answer

If I remember correctly, you can add one search condition to the matched line, i.e.

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)
Comments