Tutorial : DB2 V9 MERGE (A.K.A UPSERT)

by toy

Well, I am not a DBA and I don’t know something like this very much. But my company encourage me to take a course on DB2 V.9. And I have one command that I wanted to remember and I know that it will benefit me later on. MERGE

So, basically, you have to table and you want to merge it together. I will explain it in a real table.

T1 M1
ID AMOUNT
1 100
2 200
.
ID AMOUNT
1 100
2 200
3 200

So, we have two tables, M1 and T1. T1 has a row number 3 that M1 doesn’t have. So, if you run the MERGE command. First DB2 will UPDATE everything that the first table has in the second table. Moreover, a little bit more extra in T1 row number 3 that M1 doesn’t have. DB2 will INSERT it into T1.

Therefore, this will save you a huge amount of time to do SELECT whether T1 has row number and M1 has row number and UPDATE it. And check again and again. You could do it in one command.

1
2
3
4
5
6
7
8
MERGE INTO M1

USING ( SELECT ID, AMOUNT FROM M1 )

AS T1 (ID, AMOUNT)

ON ( M1.ID = T1.ID )

WHEN MATCHED THEN

 UPDATE SET AMOUNT = AMOUNT + T1.AMOUNT

WHEN NOT MATCHED THEN

 INSERT ( ID, AMOUNT ) VALUES (T1.ID, T1.AMOUNT);