The Merge method in Oracle

Look at the SQL statement first: Merge into employee E using Emps em on (e.emp_id=em.emp_id)

When matched then update set E.emp_name=em.emp_name

When isn't matched then insert values (em.emp_id,em.emp_name)

1. Update the data in the Emps table to the employee table, match the primary key, execute the UPDATE statement if the primary key matches, and if the INSERT statement does not match, be aware that if the data in the Emps table matches the data of the employee table, You cannot write an INSERT statement, or you can refer to a case where a match is handled, that is, there is no when matched statement

2.using can also be used in the view or sub-query, such as the above example can also be written as:

Merge into employee e using (SELECT * from Emps) em on (e.emp_id=em.emp_id)

When matched then update set E.emp_name=em.emp_name

When isn't matched then insert values (em.emp_id,em.emp_name)

The 3.update and insert clauses can be added with a WHERE clause:

Merge into employee e using (SELECT * from Emps) em on (e.emp_id=em.emp_id)

When matched and update set e.emp_name=em.emp_name where Em.emp_name like '% of% '

When isn't matched then inserts values (em.emp_id,em.emp_name) where em.emp_name like '% sheet %'

That is, add only the data containing the sheets in the Emps table.

The 4.update clause can then delete some unwanted data with the DELETE clause, and delete can only be used with the UPDATE clause

Merge into employee e using (SELECT * from Emps) em on (e.emp_id=em.emp_id)

When matched then

Update set E.emp_name=em.emp_name delete where e.emp_id=em.emp_id where em.emp_name like '% sheet %'

When isn't matched then insert values (em.emp_id,em.emp_name)

That is, do not update the data in the Emps table that contains a single word

The Merge method in Oracle