I. usage

You can update or insert tables from one or more sources at the same time, and delete a large amount of data that is often used to operate, that is, the efficiency is very high when updating or inserting large quantities of data.

Two. Grammar

merge into table_name  alias1   --Alias can be used for tables requiring operations
using (table|view|sub_query) alias2      --Data sources can be tables, views, subqueries
on (join condition)   --Correlation condition
when matched then              --Update, delete, insert when association conditions are establishedwhereSome are optional 
  update  table_name set  col1=colvalue  where......
   --To update
   delete from table_name  where  col2=colvalue  where......--Delete can only update delete or delete without update
when not matched then      --When the related conditions are not established   0racle 10  You can not use it here in the future 
  insert (col3) values (col3values)  where......;

Be careful
1. Only the operation table will be operated, and the source table will not change
2. It's not necessary to write all the update, delete and insert operations. It can be based on the actual situation

3, Example demonstration

There are three tables: dept (Department id, department name), salary (employee id, salary),
Employee form (employee id, name, position, Department, working years)
The specific data are as follows:
dept:

salary:

Employee table

It can be seen that there is no Zhuge Liang's information in the wage table. Now we are required to delete the wage information of Ma livestock in the wage table, and increase the salary of the employees whose department name is the position of the sales department by 20% (note that we don't know the department code of the information technology department here, or need to close the Department information table), insert the work number 1012, and the salary is 6000.
If there is no merge into, we need to execute three separate sql. The sql written separately is as follows:

  --delete
  delete from salary t
   where exists (select 1
            from employess a
           where a.e_id = t.e_id
             and a.e_name = 'Zhang Fei')
   --To update
             update salary t
              set t.e_sal = t.e_id * 1.2
            where exists (select 1
                     from employess a, dept b
                    where a.e_id = t.e_id
                      and a.d_id = b.dept_id
                      and a.e_position = 'staff'
                      and b.d_name = 'Sales Department')

    --insert
   insert into salary values('1012',6000);

But with merge into, we can do it in one sql:

            merge into salary t
            using (select t1.e_id,
                          t1.e_name,
                          t1.e_position,
                          t2.dept_id,
                          t2.d_name
                     from employess t1, dept t2
                    where t1.d_id = t2.dept_id) e
            on (t.e_id = e.e_id)
            when matched then
              update
                 set t.e_sal = t.e_sal * 1.2
               where e.e_position = 'staff'
                 and e.d_name = 'Sales Department' 
               delete where (e.e_name='Zhang Fei')
            when not matched
               then
              insert (e_id, e_sal) values ('1012', 6000);

results of enforcement
''
It can be seen that the information of Zhang Fei of job No. 1011 in the payroll has been deleted
The salary of 1010 is increased by 300 and the salary information of 1012 is increased at the same time