MERGE语法

 

 

WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]

在语法中DELETE 之前不常用，ORACLE解释为：

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

使用delete where_clause 处理的数据是目标表中被merge 操作更新后数据，也就是被update set 更新后的数据，

即：以下数据将被删除：

1、符合ON条件。

2、被update 更新后符合delete where 条件的数据。