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.
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......;
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:
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