CODEWITHSUNDEEP
sqldb2
amdev
amdev

Reputation: 3249

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

Upvotes: 7

Views: 24506

Answers (1)

Chris
Chris

Reputation: 340

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)

Upvotes: 18

Related Questions