Programming Blog

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

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.

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);

Share on Facebook

One asian in Britain.

In the economic down turn, I have an interesting idea at least for me to experiment on how one asian man can be living in the UK. I know that this is the the right time, it is not always the right time actually. I don’t know. I hope that everybody could have at least one chance in their live.

I am going to the UK, because I really do not like my country now. I have high hope. However, I have at least two years to prove myself that am I capable of doing something that everyday says that it is hard YOU CAN’T DO IT MAN. Well, I’m gonna prove it later.

Share on Facebook

How-to : Install tomcat6 Ubuntu

1.

2. Insert this lines into /var/lib/tomcat6/conf/tomcat-users.xml

1
2
3
4
5
6
7
8
9
10
<tomcat-users>

    <role rolename="admin"/>

    <role rolename="manager"/>

    <role rolename="tomcat"/>

    <role rolename="role1"/>

    <user username="admin" password="your_password" roles="manager,admin"/>

    <user username="tomcat" password="tomcat" roles="tomcat"/>

    <user username="role1" password="tomcat" roles="role1"/>

    <user username="both" password="tomcat" roles="tomcat,role1"/>

</tomcat-users>

3. Restart tomcat

Share on Facebook