《Oracle SQL疑难解析》——1.16 基于条件的插入或修改

1.16 基于条件的插入或修改

Oracle SQL疑难解析

1.16.1 要解决的问题

我们需要在表中插入一条记录，插入前根据KEY标识符判断。如果标识符不存在，则插入新记录；如果标识符已经存在，则根据语句中所给的新值对原记录中的字段进行更新。

1.16.2 解决方法

MERGE语句的功能是将新数据插入一个表中。记录是否存在，由主（PRIMARY）键进行判断。如果主键不存在于表中，则插入该行。如果主键匹配表中已有的行，则通过匹配键的其他详细信息更新该行。

在示例中，假设要把NEW_COUNTRIES 表中一些国家的信息添加到HR.COUNTIES表中：

merge into hr.countries c
using
　(select country_id, country_name
　 from hr.new_countries) nc
on (c.country_id = nc.country_id)
when matched then
　update set c.country_name = nc.country_name
when not matched then
　insert (c.country_id, c.country_name)
　values (nc.country_id, nc.country_name);

1.16.3 数据库如何工作

如果直接把HR.NEW_COUNTRIES中的源数据插入HR.COUNTRIES目标表中，有可能会失败，由于HR.COUNTRIES表中可能已经有此国家的记录，因此造成主键值重复而插入失败。MERGE语句可以通过逻辑分支子句的ON来处理存在和不存在这两种情况。

通常，ON子句指定如何在源表和目标表之间匹配主键或唯一键。在本例句中，用COUNTRY_ID来匹配：

on (c.country_id = nc.country_id)

ON 子句后面跟着另两个子句，当键值相匹配时，执行“WHEN MATCHED CLAUSE”子句内容，当键值不匹配时，执行“WHEN NOT MATCHED”子句，会把新的记录插入目标表中。

match和not-match子句都可以包含更多的筛选条件，当满足删除条件时，也可以删除记录：

merge into hr.countries c
using
　(select country_id, country_name, region_id
　 from hr.new_countries) nc
on (c.country_id = nc.country_id)
when matched then
　update set c.country_name = nc.country_name,
　　c.region_id = nc.region_id
　delete where nc.region_id = 4
when not matched then
　insert (c.country_id, c.country_name, c.region_id)
　values (nc.country_id, nc.country_name, nc.region_id)
　where (nc.region_id != 4);

在这个修改后的版本中，除了REGION_ID的新值是4的行，其他根据COUNTRY_ID匹配的HR.CONTRIES的行都会修改COUNTR_NAME的值，而REGION_ID是4的行最终会删除。没有匹配上的行，除了REGION_ID的值是4的行，其他行都会被插入HR.COUNTRIES中，而REGION_ID的值是4的行会被忽略。