关联更新是指用一个表的某几个列的值去更新另一个表中一个或几个列的值，这是一种比较常见的操作，Oracle数据库里由多个写法可以完成这个操作。
1 数据准备
这里使用Oracle示例数据库sh里的CUSTOMERS表作为示范，这个表有55000行，表的结构如下所示：
SELECT * FROM CUSTOMERS c WHERE rownum<=1;
CUST_ID|CUST_FIRST_NAME|CUST_LAST_NAME|CUST_GENDER|CUST_YEAR_OF_BIRTH|CUST_MARITAL_STATUS|CUST_STREET_ADDRESS|CUST_POSTAL_CODE|CUST_CITY|CUST_CITY_ID|CUST_STATE_PROVINCE|CUST_STATE_PROVINCE_ID|COUNTRY_ID|CUST_MAIN_PHONE_NUMBER|CUST_INCOME_LEVEL |CUST_CREDIT_LIMIT|CUST_EMAIL |CUST_TOTAL |CUST_TOTAL_ID|CUST_SRC_ID|CUST_EFF_FROM |CUST_EFF_TO|CUST_VALID|
-------+---------------+--------------+-----------+------------------+-------------------+-------------------+----------------+---------+------------+-------------------+----------------------+----------+----------------------+------------------+-----------------+-------------------------+--------------+-------------+-----------+-----------------------+-----------+----------+
7670|Abel |Vance |M | 1976| |17 Sacramento Court|41616 |Ruinen | 52177|Drenthe | 52579| 52770|113-667-4744 |B: 30,000 - 49,999| 1500|Vance@company.example.com|Customer total| 52772| |2019-01-01 00:00:00.000| |I |
抽取欧洲的客户创建另一个表作为更新的源表
CREATE TABLE customer_eur AS SELECT * FROM CUSTOMERS cu WHERE cu.COUNTRY_ID IN (SELECT COUNTRY_ID FROM COUNTRIES c WHERE c.COUNTRY_REGION = 'Europe' );
假设customer_eur里customer的CUST_CREDIT_LIMIT发生了变化，需要将变化的数据更新到customer表里去。
2 直接更新
UPDATE sh.CUSTOMERS c SET CUST_CREDIT_LIMIT = (SELECT CUST_CREDIT_LIMIT FROM sh.customer_eur e WHERE e.CUST_ID = c.CUST_ID)
WHERE c.COUNTRY_ID = 52776;
---执行计划如下：
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 8173 | 183K| 684 (1)| 00:00:01 |
| 1 | UPDATE | CUSTOMERS | | | | |
|* 2 | HASH JOIN OUTER | | 8173 | 183K| 684 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| CUSTOMERS | 8173 | 111K| 444 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| CUSTOMER_EUR | 30564 | 268K| 240 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("E"."CUST_ID"(+)="C"."CUST_ID")
3 - filter("C"."COUNTRY_ID"=52776)
Oracle将语句改为外连接方式，要更新的表是内表，更新数据的原表是目标表，这种写法有一个缺陷，如果要更新的数据在源表里不存在，会将目标表里响应的列值清空
SELECT cust_id,CUST_CREDIT_LIMIT FROM sh.customer_eur c WHERE cust_id=7670;
no rows selected
SELECT cust_id,CUST_CREDIT_LIMIT FROM sh.CUSTOMERS c WHERE cust_id=7670;
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
7670 1500
UPDATE sh.CUSTOMERS c SET CUST_CREDIT_LIMIT = (SELECT CUST_CREDIT_LIMIT FROM sh.customer_eur e WHERE e.CUST_ID = c.CUST_ID)
WHERE c.CUST_ID = 7670;
SELECT cust_id,CUST_CREDIT_LIMIT FROM sh.CUSTOMERS c WHERE cust_id=7670
SQL> /
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
7670
用户id为7670的数据在customer_eur表里不存在，在运行了更新语句之后，目标表CUSTOMERS里用户的CUST_CREDIT_LIMIT被清空。在大多数情况下，这应该不是想要的结果。如果在源表里没有数据的情况下保持目标表里的数据不变，在后面加上exists条件
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
7670 1500
UPDATE sh.CUSTOMERS c SET CUST_CREDIT_LIMIT = (SELECT CUST_CREDIT_LIMIT FROM sh.customer_eur e WHERE e.CUST_ID = c.CUST_ID)
WHERE EXISTS (SELECT 1 FROM sh.customer_eur e WHERE e.cust_id=c.CUST_ID) AND c.CUST_ID = 7670;
Updated Rows 0
SELECT cust_id,CUST_CREDIT_LIMIT FROM sh.CUSTOMERS c WHERE cust_id=7670
CUST_ID CUST_CREDIT_LIMIT
---------- -----------------
7670 1500
在加上exists条件后，语句的执行计划也发生了变化
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 22 | 3 (0)| 00:00:01 |
| 1 | UPDATE | CUSTOMERS | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 22 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 8 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_ID_CE | 1 | 5 | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_EUR | 1 | 9 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | IDX_ID_CE | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."CUST_ID"=7670)
6 - access("E"."CUST_ID"=7670)
8 - access("E"."CUST_ID"(+)=7670)
执行计划里增加了一个NESTED LOOPS SEMI，增加了语句的执行成本，如果数据量比较大时，会影响到语句的性能。
3 内联视图更新
UPDATE (
SELECT e.CUST_CREDIT_LIMIT ccl1, c.CUST_CREDIT_LIMIT ccl2
FROM sh.CUSTOMERS c INNER JOIN sh.customer_eur e ON e.cust_id=c.cust_id WHERE c.country_id=52788 )
SET ccl1=ccl2;
内联视图更新的写法，where条件既可以加在目标表上，也可以加载源表上，执行计划如下：
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 91 | 2002 | 72 (0)| 00:00:01 |
| 1 | UPDATE | CUSTOMERS | | | | |
| 2 | NESTED LOOPS | | 91 | 2002 | 72 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 91 | 2002 | 72 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 91 | 1183 | 22 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_COUNTRY_C | 91 | | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_ID_CE | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_EUR | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."COUNTRY_ID"=52788)
6 - access("E"."CUST_ID"="C"."CUST_ID")
使用inlineview，两表的连接可以是内连接，优化器可以根据情况选择合适的连接顺序，以获得更优的性能，在某些情况下可能性能会比上一节的写法好一些。
3 merge into
merge into 是Oracle独有的SQL语句，可以根据两个表的数据的匹配情况执行不同的update或insert操作，Oracle为merge
语句的优化是为执行数据集的合并而做的，而不是为了执行单行update和insert，因此在数据量大时比其它方式快很多，在多数情况下是首要的选择。本例中写法如下：
merge into customers c
using (select cust_id,CUST_CREDIT_LIMIT from customer_eur WHERE country_id=52788 ) e
on (c.cust_id = e.cust_id)
when matched then
update set c.CUST_CREDIT_LIMIT = e.CUST_CREDIT_LIMIT;
5 update from
这是Oracle近期版本才支持的写法
UPDATE SH.customers c SET c.CUST_CREDIT_LIMIT=e.CUST_CREDIT_LIMIT FROM SH.customer_eur e WHERE c.cust_id = e.cust_id AND c.COUNTRY_ID=52788;
看一下语句的执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 91 | 2002 | 72 (0)| 00:00:01 |
| 1 | UPDATE | CUSTOMERS | | | | |
| 2 | NESTED LOOPS | | 91 | 2002 | 72 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 91 | 2002 | 72 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 91 | 1183 | 22 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_COUNTRY_C | 91 | | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_ID_CE | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_EUR | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."COUNTRY_ID"=52788)
6 - access("C"."CUST_ID"="E"."CUST_ID")
语句的执行计划同前面的内联视图的更新完全相同，这个写法可能就是内联视图更新的另一种形式吧，不过更简单更直观。
5 总结
上面的几种写法中，如果需要更新的数据量比较大，应该选择merge into ，如果更新的数据比较少，在Oracle最新版本中可以选择update from，如果是老版本的Oracle数据库，推荐使用内联视图。在更新大表中的少量数据时，关联键上的索引和选择条件上的索引对性能的提升非常关键。
另外一点要注意的是，根据set语句根据关联设置的值只能是一个，如果要更新的目标表的一个值对应源表数据中的多个值，应该使用视图或者是聚合函数取得一个值后再去更新。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
