Use the merge statement to update or insert from a selected row in one table to another table

The decision whether to update or insert into the target table is based on the condition in the ON clause. It allows you to avoid multiple insert and update DML statements.

The syntax is:

MERGE into table

USING Data_source

On (condition)

When matched THEN Update_clause

When not matched THEN insert_clause;

Case:

For example, now there are a, B two tables

A

Name Sal

Zhang 34,000

Lee 45,000

Wang 55,500

B

Name Sal

Zhang 35,300

Liu 27,000

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Now there is a need to update the data in table B to list A, and if a table exists, update the SAL field, if a record does not exist, insert it.

If you use INSERT, update to implement

INSERT INTO A

SELECT * from B where b.name isn't in (select name from A);

Update A

Set a.sal = (select B.sal from b where b.name = A.name)

where A.name in (select B.name from B);

If you use Merge to implement

Merge into A

Using B

On (a.name = b.name)

When matched then

Update

Set a.sal = B.sal

When not matched then

Insert (A.name,a.sal)

Values (b.name,b.sal);