Db2, Merge into Update If Value Different

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

1 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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Elena Rostova
Author

Elena Rostova

Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.