DB2 Merge Update

The DB2 Merge statement is powerful in that it merges data from one table into another, and can be inserted, deleted, updated, and so on while merging. Let's start with a simple example, suppose you define an employee table (Employe), a manager table (manager), as follows:

keywords, parameters

INTO clause specifies the target table using clause in the INTO clause that you want to modify or insert data to specify the data source to modify or insert in the Using clause. The data source can be a table, a view, or a subquery statement. The ON clause specifies the satisfying condition to perform the insert or modify in the ON clause. When matched | Not matched uses this clause to inform the database how to make appropriate actions on the result of satisfying or not satisfying the condition. You can use the following two types of clauses.
The MERGE_UPDATE clause merge_update clause performs a modification to the field values in the target table. Executes when an ON clause condition is met. If the modification clause is executed, the modification trigger on the target table is triggered.
Restriction: When modifying a view, you cannot specify a default value MERGE_INSERT clause Merge_insert clause executes when the ON clause condition is not met, the data is inserted into the target table. If the INSERT clause is executed, the INSERT trigger on the target table is triggered. Restriction: You cannot specify a default value when you modify a view [C-sharp]View PlainCopyprint?

2. ---Employees table (Employe)
4. CREATE TABLE Employe (
6. Employeid INTEGER not NULL,---employee number
8. Name VARCHAR () not NULL,---name
10. SALARY DOUBLE---Salary
12. );
14. INSERT into Employe (employeid,name,salary) VALUES
16. (1,' Zhang San ', +),
18. (2,' John Doe ',
20. (3,' Harry ', +),
22. (4,' Zhao Liu ', 4000),
24. (5,' High seven ', 5000);
26. --Manager table
28. CREATE TABLE MANAGER (
30. Employeid INTEGER not NULL,---Manager number
32. Name VARCHAR () not NULL,---name
34. SALARY DOUBLE---Salary
36. );
38. INSERT into MANAGER (managerid,name,salary) VALUES
40. (3,' Harry ',
42. (4,' Zhao Liu ', 6000);

After a while, you find that the data model, or table structure design is a big failure, managers and employees are employees, why design two tables? You need to correct the error, so you decide to delete the manager table, merge the data from the manager table into the Employe table, and carefully analyze that the Harry exists in two tables (probably a good job), and Liu Ba does not exist in the Employe table, now, We require that the manager that does not exist in the Employe table be inserted into the Employe table, where there is an updated salary. What should we do? This problem is not difficult, usually, we can take two steps, as follows:

[C-sharp]View PlainCopyprint?

1. --Update the existing
2. UPDATE Employe as EM SET salary= (SELECT SALARY from MANAGER WHERE managerid=em. Employeid)
3. WHERE Employeid in (
4. SELECT ManagerID from MANAGER
5. );
6. ---insert non-existent
7. INSERT into Employe (employeid,name,salary)
8. SELECT managerid,name,salary from MANAGER WHERE ManagerID not in (
9. SELECT Employeid from Employe
10. );

The above processing is possible, but we can also have a simpler way, that is, with the merge statement, as follows:

[C-sharp]View PlainCopyprint?

1. MERGE into Employe as EM
2. USING MANAGER as MA
3. On EM. Employeid=ma. ManagerID
4. When matched then UPDATE SET EM. Salary=ma. SALARY
5. When isn't 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 manager's salary, assuming that if the manager's Salary > Employee table (Employe) 's salary is updated, it will not be updated. As follows:

[C-sharp]View PlainCopyprint?

1. MERGE into Employe as EM
2. USING MANAGER as MA
3. On EM. Employeid=ma. ManagerID
4. When matched and EM. Salary<ma. SALARY then UPDATE SET EM. Salary=ma. SALARY
5. When isn't matched then INSERT VALUES (MA. Managerid,ma.name,ma. SALARY)
6. ELSE IGNORE;

Not careful friend may not see the above two statements of the difference, haha, please carefully compare these two statements. The above statement is more than the else ignore statement, which means just as it means in English, and other cases are ignored. If you think there should be no em.salary>ma in theory. Salary data, if any, indicates a problem, you want to throw an exception, what to do? As follows:

[C-sharp]View PlainCopyprint?

1. MERGE into Employe as EM
2. USING MANAGER as MA
3. On EM. Employeid=ma. ManagerID
4. When matched and EM. Salary<ma. SALARY then UPDATE SET EM. Salary=ma. SALARY
5. When matched and EM. Salary>ma. SALARY then SIGNAL SQLSTATE ' 70001 ' SET message_text = ' EM. Salary>ma. SALARY '
6. When isn't matched then INSERT VALUES (MA. Managerid,ma.name,ma. SALARY)
7. ELSE IGNORE;

For Em.salary>ma. Salary situation, if you do not want to throw an exception, but delete the data in Employe, how to do? As follows:

[C-sharp]View PlainCopyprint?

1. MERGE into Employe as EM
2. USING MANAGER as MA
3. On EM. Employeid=ma. ManagerID
4. When matched and EM. Salary<ma. SALARY then UPDATE SET EM. Salary=ma. SALARY
5. When matched and EM. Salary>ma. SALARY then DELETE
6. When isn't matched then INSERT VALUES (MA. Managerid,ma.name,ma. SALARY)
7. ELSE IGNORE;

Update

Multiple tables update multiple fields, and you need to add the where condition to limit the update content.

Table structure:
CREATE TABLE atest
(ID INTEGER,
NAME VARCHAR (256),
CODE INTEGER,
NAME2 VARCHAR (256)
)

CREATE TABLE Btest
(ID INTEGER,
CODE INTEGER
)

CREATE TABLE CTest
(ID INTEGER,
NAME VARCHAR (256),
NAME2 VARCHAR (256)
)

SQL statements:
One table updates the fields of another table:
Update atest
Set Atest.name= (select Ctest.name from ctest where atest.id = ctest.id)
where Atest.id in (select Ctest.id from CTest);

Two table Associations update fields for another table:
Update atest
Set (name,name2) = (SELECT case when Ctest.name was NULL then Atest.name ELSE ctest.name END, case when CTest. NAME2 is NULL and then atest. NAME2 ELSE CTest. NAME2 END from Btest left JOIN ctest on btest.id = Ctest.id WHERE atest. CODE = Btest. CODE)
WHERE atest. CODE in (SELECT btest. CODE from Btest);


The other one: http://blog.csdn.net/Bobwu/archive/2009/01/13/3768636.aspx
1.
Declare
Cursor T1 is a SELECT * from TableName;
Begin
For rec in T1 loop
Update TableName t set T.detail=rec.jieshao where T.objectid=rec.objid;
End Loop;
End

2.

Update student Set (NAME,ID) =
(select name, id from (select Student.rowid rd,student1.name,student1.id from Student1,student where student1.int_id =student.int_id) tmp
where STUDENT.ROWID=TMP.RD);
Commit

3.

Update Test_a a set (a.name,a.age) =
(select B.name,b.age from Test_b b where a.id = b.id) where exists
(SELECT * from Test_b C where c.id=a.id)

4.

UPDATE t_a SET djrq=
(
SELECT Djrq from t_b WHERE t_a.id = t_b.id
WHERE ROWNUM = 1
)
WHERE T_a.id in
(
SELECT ID from T_b WHERE jwh= ' xx village '
)

5.

Update TBL1 A
Set (A.col1, a.col2) = (select B.col1, B.col2
From TBL2 b
where A.key = B.key)
where A.key in (select Key from TBL2)

DB2 Merge Update