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

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)

In SQL Server, http://www.withdata.com/ad/sql-server/replace-update-or-insert-a-row-into-sql-server-table.html .

In Oracle, http://www.withdata.com/ad/oracle/replace-update-or-insert-a-row-into-oracle-table-merge-into.html

In PostgreSQL, http://www.withdata.com/ad/postgresql/replace-update-or-insert-a-row-into-postgresql-table.html

In Sqlite, http://www.withdata.com/ad/sqlite/replace-update-or-insert-a-row-into-sqlite-table.html

Some DB2 tools you can try: https://www.withdata.com/db2/