两张表的关联更新在实际工作中经常会遇到，例如工作中由于程序的BUG，或者数据模型的改变，以至于会对数据进行批量的修改。
通常情况下都是通过一张表的某个字段或者某些字段来修改另外一种表中的数据。下面我们来模拟一下这个过程：
有两张表:testa和testb ，两张表的结构一样，但是表中的数据不一致，我们希望以testb表为基准，更新testa表的name和age字段，testa表有10条记录，testb表有5条记录
SQL> create table testa(
2 id int,
3 name varchar2(20),
4 age int
5 );
表已创建。
SQL> create table testb(
2 id int,
3 name varchar2(20),
4 age int
5 );
表已创建。
SQL> select * from testa;
未选定行
SQL> select * from testb;
未选定行
SQL> begin
2 for j in 1..10 loop
3 insert into testa values (j,'macro'||j,j);
4 if(mod(j,2)=0) then
5 insert into testb values (j,'macro'||(10-j),10-j);
6 end if;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL> rollback;
回退已完成。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL> update testa set
2 name=(select b.name from testb b where b.id=testa.id ),
3 age=(select b.age from testb b where b.id=testa.id );
已更新10行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1
2 macro8 8
3
4 macro6 6
5
6 macro4 4
7
8 macro2 2
9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
第二种方法：
SQL> rollback;
回退已完成。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL> update testa set
2 (name,age)=(select name,age from testb where testa.id=testb.id)
3 where exists(select 1 from testb where testa.id=testb.id);
已更新5行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro8 8
3 macro3 3
4 macro6 6
5 macro5 5
6 macro4 4
7 macro7 7
8 macro2 2
9 macro9 9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
下面对4种方法的性能进行一下简单的分析：
第二种方法：
第三种方法：
通常情况下都是通过一张表的某个字段或者某些字段来修改另外一种表中的数据。下面我们来模拟一下这个过程：
有两张表:testa和testb ，两张表的结构一样，但是表中的数据不一致，我们希望以testb表为基准，更新testa表的name和age字段，testa表有10条记录，testb表有5条记录
SQL> create table testa(
2 id int,
3 name varchar2(20),
4 age int
5 );
表已创建。
SQL> create table testb(
2 id int,
3 name varchar2(20),
4 age int
5 );
表已创建。
SQL> select * from testa;
未选定行
SQL> select * from testb;
未选定行
向表中插入数据:
SQL> begin
2 for j in 1..10 loop
3 insert into testa values (j,'macro'||j,j);
4 if(mod(j,2)=0) then
5 insert into testb values (j,'macro'||(10-j),10-j);
6 end if;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
第一种方法：
update testa set
name=(select b.name from testb b where b.id=testa.id ),
age=(select b.age from testb b where b.id=testa.id )
where exists(select 1 from testb where testa.id=testb.id);
通过子查询进行修改。
SQL> update testa set
2 name=(select b.name from testb b where b.id=testa.id ),
3 age=(select b.age from testb b where b.id=testa.id )
4 where exists(select 1 from testb where testa.id=testb.id);
已更新5行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro8 8
3 macro3 3
4 macro6 6
5 macro5 5
6 macro4 4
7 macro7 7
8 macro2 2
9 macro9 9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL>
需要注意的是被更新的表一定要加限定条件，也就是哪些数据需要被更新 (where exists(select 1 from testb where testa.id=testb.id))，否则会将原表中数据修改错误。我们将上面语句的条件去掉试试。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL> update testa set
2 name=(select b.name from testb b where b.id=testa.id ),
3 age=(select b.age from testb b where b.id=testa.id );
已更新10行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1
2 macro8 8
3
4 macro6 6
5
6 macro4 4
7
8 macro2 2
9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
我们未加条件时，表中所有记录被修改了，事实上，我们只要求修改5行记录，此时表中数据不是我们想要看到的结果，说严重点由于我们的工作失误造成了错误数据。
所以修改时一定要加好限定条件，同时在这种批量修改数据时，一定要先做好备份，可以通过create table tab_bak select * from tab;
如果表数据量大可以加上nologging，create table tab_bak nologging as select * from tab; 如果服务器负载较低，也可以使用parallel进行备份的加速
update testa set
(name,age)=(select name,age from testb where testa.id=testb.id)
where exists(select 1 from testb where testa.id=testb.id);
严格意义上说这也不算第二种方法，因为它使用的还是子查询，只不过是将更新的列统一放在一个查询中，同时他使用一个子查询将结果查询出来，在SQL性能上来说较第一种方法要好一些
回退已完成。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro2 2
3 macro3 3
4 macro4 4
5 macro5 5
6 macro6 6
7 macro7 7
8 macro8 8
9 macro9 9
10 macro10 10
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
SQL> update testa set
2 (name,age)=(select name,age from testb where testa.id=testb.id)
3 where exists(select 1 from testb where testa.id=testb.id);
已更新5行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro8 8
3 macro3 3
4 macro6 6
5 macro5 5
6 macro4 4
7 macro7 7
8 macro2 2
9 macro9 9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
第三种方法：
UPDATE (SELECT a.name namea,
a.age agea,
b.name nameb,
b.age ageb
FROM testa a,
testb b
WHERE a.id = b.id)
SET namea = nameb,
agea = ageb;
通过视图来进行更新，但是表中需要有主键，否则会出现ORA-01779字段。
SQL> UPDATE (SELECT a.name namea,
2 a.age agea,
3 b.name nameb,
4 b.age ageb
5 FROM testa a,
6 testb b
7 WHERE a.id = b.id)
8 SET namea = nameb,
9 agea = ageb;
SET namea = nameb,
*
第 8 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列
SQL> alter table testa add constraint pk_testa primary key(id);
表已更改。
SQL> alter table testb add constraint pk_testb primary key(id);
表已更改。
SQL> UPDATE (SELECT a.name namea,
2 a.age agea,
3 b.name nameb,
4 b.age ageb
5 FROM testa a,
6 testb b
7 WHERE a.id = b.id)
8 SET namea = nameb,
9 agea = ageb;
已更新5行。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro8 8
3 macro3 3
4 macro6 6
5 macro5 5
6 macro4 4
7 macro7 7
8 macro2 2
9 macro9 9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
第四种方法：
MERGE INTO testa a
USING testb b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE
SET a.name = b.name,
a.age = b.age;
通过merge可以轻松的实现这样的需求，merge into很强大，后续我会专门讲解一下它的用法。
SQL> MERGE INTO testa a
2 USING testb b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET a.name = b.name,
7 a.age = b.age;
5 行已合并。
SQL> select * from testa;
ID NAME AGE
---------- -------------------- ----------
1 macro1 1
2 macro8 8
3 macro3 3
4 macro6 6
5 macro5 5
6 macro4 4
7 macro7 7
8 macro2 2
9 macro9 9
10 macro0 0
已选择10行。
SQL> select * from testb;
ID NAME AGE
---------- -------------------- ----------
2 macro8 8
4 macro6 6
6 macro4 4
8 macro2 2
10 macro0 0
第一种方法：
SQL> update testa set
2 name=(select b.name from testb b where b.id=testa.id ),
3 age=(select b.age from testb b where b.id=testa.id )
4 where exists(select 1 from testb where testa.id=testb.id);
已更新5行。
执行计划
----------------------------------------------------------
Plan hash value: 1469534405
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 255 | 23 (44)| 00:00:0
| 1 | UPDATE | TESTA | | | |
| 2 | NESTED LOOPS SEMI | | 5 | 255 | 3 (0)| 00:00:0
| 3 | TABLE ACCESS FULL | TESTA | 10 | 380 | 3 (0)| 00:00:0
|* 4 | INDEX UNIQUE SCAN | PK_TESTB | 3 | 39 | 0 (0)| 00:00:0
| 5 | TABLE ACCESS BY INDEX ROWID| TESTB | 1 | 25 | 1 (0)| 00:00:0
|* 6 | INDEX UNIQUE SCAN | PK_TESTB | 1 | | 0 (0)| 00:00:0
| 7 | TABLE ACCESS BY INDEX ROWID| TESTB | 1 | 26 | 1 (0)| 00:00:0
|* 8 | INDEX UNIQUE SCAN | PK_TESTB | 1 | | 0 (0)| 00:00:0
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TESTA"."ID"="TESTB"."ID")
6 - access("B"."ID"=:B1)
8 - access("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
1 db block gets
31 consistent gets
0 physical reads
748 redo size
837 bytes sent via SQL*Net to client
948 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
第二种方法：
SQL> update testa set
2 (name,age)=(select name,age from testb where testa.id=testb.id)
3 where exists(select 1 from testb where testa.id=testb.id);
已更新5行。
执行计划
----------------------------------------------------------
Plan hash value: 4210035906
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 255 | 23 (44)| 00:00:01 |
| 1 | UPDATE | TESTA | | | | |
| 2 | NESTED LOOPS SEMI | | 5 | 255 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TESTA | 10 | 380 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_TESTB | 3 | 39 | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TESTB | 1 | 38 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_TESTB | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TESTA"."ID"="TESTB"."ID")
6 - access("TESTB"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
1 db block gets
21 consistent gets
0 physical reads
748 redo size
838 bytes sent via SQL*Net to client
896 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
第三种方法：
SQL> UPDATE (SELECT a.name namea,
2 a.age agea,
3 b.name nameb,
4 b.age ageb
5 FROM testa a,
6 testb b
7 WHERE a.id = b.id)
8 SET namea = nameb,
9 agea = ageb;
已更新5行。
执行计划
----------------------------------------------------------
Plan hash value: 1547926025
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 380 |
| 1 | UPDATE | TESTA | | |
| 2 | MERGE JOIN | | 5 | 380 |
| 3 | TABLE ACCESS BY INDEX ROWID| TESTB | 5 | 190 |
| 4 | INDEX FULL SCAN | PK_TESTB | 5 | |
|* 5 | SORT JOIN | | 10 | 380 |
| 6 | TABLE ACCESS FULL | TESTA | 10 | 380 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
5 db block gets
9 consistent gets
0 physical reads
1324 redo size
838 bytes sent via SQL*Net to client
982 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
第四种方法：
SQL> MERGE INTO testa a
2 USING testb b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET a.name = b.name,
7 a.age = b.age;
5 行已合并。
执行计划
----------------------------------------------------------
Plan hash value: 3899243144
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 5 | 250 | 6 (17)| 00:00:01 |
| 1 | MERGE | TESTA | | | | |
| 2 | VIEW | | | | | |
| 3 | MERGE JOIN | | 5 | 440 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TESTB | 5 | 190 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_TESTB | 5 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 10 | 500 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TESTA | 10 | 500 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
5 db block gets
9 consistent gets
0 physical reads
1324 redo size
839 bytes sent via SQL*Net to client
897 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
总结一下：
1.oracle中两张表或者多张表关联更新的方法主要就上面讲到的几种：
子查询
定义视图更新
merge into
2.从写法上讲：
子查询较为普及，估计使用者十之七八
通过视图的方法，写法较为繁琐，局限性比较大(关联字段不允许重复)
merge into虽写法教特殊，但是强大的功能还是很值得我们学习的
3.从性能上讲：
视图和merge into性能优于子查询
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28392775/viewspace-759719/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
[BAS]Oracle 创建数据库
请登录后发表评论
登录
全部评论
