Usage of DB2 Merge statements

The DB2 Merge statement is a commonly used DB2 statement. The DB2 Merge statement is described in detail below. If you are interested in this, take a look.

The DB2 Merge statement is very powerful. It can Merge data in one table into another, and insert, delete, update, and other operations can be performed at the same time. Let's take a look at a simple example. Suppose you have defined an employee table employe and a manager table manager), as shown below:

--- Employee table EMPLOYE)
Create table employe (
Employeid integer not null, --- employee ID
Name varchar (20) not null, --- NAME
Salary double --- SALARY
);
Insert into employe (EMPLOYEID, NAME, SALARY) VALUES
(1, 'zhang san', 1000 ),
(2, 'Li si', 2000 ),
(3, 'wang wu', 3000 ),
(4, 'zhao liu', 4000 ),
(5, 'high 7', 5000 );
-- MANAGER table MANAGER)
Create table manager (
Employeid integer not null, --- manager number
Name varchar (20) not null, --- NAME
Salary double --- SALARY
);
Insert into manager (MANAGERID, NAME, SALARY) VALUES
(3, 'wang wu', 5000 ),
(4, 'zhao liu', 6000 );
--- Employee table EMPLOYE)
Create table employe (
Employeid integer not null, --- employee ID
Name varchar (20) not null, --- NAME
Salary double --- SALARY
);
Insert into employe (EMPLOYEID, NAME, SALARY) VALUES
(1, 'zhang san', 1000 ),
(2, 'Li si', 2000 ),
(3, 'wang wu', 3000 ),
(4, 'zhao liu', 4000 ),
(5, 'high 7', 5000 );
-- MANAGER table MANAGER)
Create table manager (
Employeid integer not null, --- manager number
Name varchar (20) not null, --- NAME
Salary double --- SALARY
);
Insert into manager (MANAGERID, NAME, SALARY) VALUES
(3, 'wang wu', 5000 ),
(4, 'zhao liu', 6000 );

After a while, you discovered that such a data model, or table structure design, was a big failure. Why should we design two tables for both managers and employees? After an error is found, you need to correct the error. Therefore, you have decided to delete the MANAGER table MANAGER and merge the data in the MANAGER table into the EMPLOYE table, wang Wu may be well promoted in both tables), but Liu Ba does not exist in the EMPLOYE table. Now, we need to insert managers that do not exist in the EMPLOYE table into the EMPLOYE table, existing updated salary. What should I do? This problem is not difficult. Generally, we can take two steps as follows:

-- Update existing
Update employe as em set salary = (select salary from manager where managerid = EM. EMPLOYEID)
Where employeid in (
SELECT MANAGERID FROM MANAGER
);
--- Insert nonexistent
Insert into employe (EMPLOYEID, NAME, SALARY)
Select managerid, NAME, salary from manager where managerid not in (
SELECT EMPLOYEID FROM EMPLOYE
);
-- Update existing
Update employe as em set salary = (select salary from manager where managerid = EM. EMPLOYEID)
Where employeid in (
SELECT MANAGERID FROM MANAGER
);
--- Insert nonexistent
Insert into employe (EMPLOYEID, NAME, SALARY)
Select managerid, NAME, salary from manager where managerid not in (
SELECT EMPLOYEID FROM EMPLOYE
);

The above processing is acceptable, but we can also have a simpler method, that is, using the Merge statement, as shown below:

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched then update set em. SALARY = MA. SALARY
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY );
MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched then update set em. SALARY = MA. SALARY
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY );

In the above process, we updated the salary of the employee table EMPLOYE with the salary of the MANAGER table), assuming that the current requirement is, if the MANAGER table is) the salary> employee table EMPLOYE) the salary is updated; otherwise, the salary is not updated. What should I do? As follows:

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY );
MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY );

A friend who is not careful may not see the difference between the above two statements. Haha, Please carefully compare these two statements. In the preceding statement, the else ignore statement is added, which means exactly as it means in English. In other cases, the else ignore statement is ignored and not processed. In theory, if you believe that the data from EM. SALARY> MA. SALARY does not exist, if yes, it means there is a problem. What should you do if you want to throw an exception? As follows:

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When matched and em. SALARY> MA. salary then signal sqlstate '000000' SET MESSAGE_TEXT = 'em. SALARY> MA. SALARY'
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY)
Else ignore;
MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When matched and em. SALARY> MA. salary then signal sqlstate '000000' SET MESSAGE_TEXT = 'em. SALARY> MA. SALARY'
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY)
Else ignore;

In the case of EM. SALARY> MA. SALARY, what if you do not want to throw an exception but delete the data in EMPLOYE? As follows:

MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When matched and em. SALARY> MA. SALARY THEN DELETE
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY)
Else ignore;
MERGE INTO EMPLOYE AS EM
USING MANAGER AS MA
On em. EMPLOYEID = MA. MANAGERID
When matched and em. SALARY <MA. salary then update set em. SALARY = MA. SALARY
When matched and em. SALARY> MA. SALARY THEN DELETE
When not matched then insert values (MA. MANAGERID, MA. NAME, MA. SALARY)
Else ignore;

The above briefly introduces the use of the Merge statement. Its application is not just the case described above. In fact, it can be applied to many other statements that are not easy to handle. You need to find out, remember that practice makes perfect.

Principle of DB2 cursor

Two ways to store db2 logs

Common DB2 cycle usage

Four Common DB2 cycle statements

Implementation of creating a database in DB2