Replace (Update/Insert) a row into DB2 table – Merge into

Shiji Pan

12 years ago

In MySQL, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”.

How to do it in DB2?

Use “merge into” .

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
    WHEN NOT MATCHED THEN
        INSERT (C1, C2, C3)
        VALUES (merge.C1, merge.C2, merge.C3)

For example:

MERGE INTO employees AS tab
USING (VALUES
        (123456,'smith','bob')
    ) AS merge (id,last_name,first_name)
    ON tab.id = merge.id
    WHEN MATCHED THEN
        UPDATE SET tab.id = merge.id,
                   tab.last_name = merge.last_name,
                   tab.first_name = merge.first_name
    WHEN NOT MATCHED THEN
        INSERT (id,last_name,first_name)
        VALUES (merge.id, merge.last_name, merge.first_name)