Oracle 多表关联update

前言

在日常工作中经常会遇到用A表的1个或多个字段的值更新B表的数据（多表关联更新的需求），结合自己经常使用的SQL及网上的资料总结了如下几个SQL，方便日后查询。

测试数据：

create table t1 (id number,name varchar(10));
create table t2 (id number,name varchar(10));

insert into t1 values(1,'a');
insert into t1 values(2,'b');
insert into t1 values(3,'c');
insert into t1 values(4,'d');
insert into t1 values(5,'e');
insert into t1 values(6,'f');
insert into t1 values(7,'g');


insert into t2 values(1,'A');
insert into t2 values(2,'B');
insert into t2 values(3,'C');
insert into t2 values(4,'D');
insert into t2 values(5,'E');
insert into t2 values(6,'F');
commit;

1、利用SQL拼接update(适合逻辑简单并数据量较少的表)

SQL>  select 'update t2 set t2.name='||''''||t1.name||''''||' where t2.id='|| t1.id||';' from t1;
'UPDATET2SETT2.NAME='||''''||T1.NAME||''''||'WHERET2.ID='||T1.ID||';'
--------------------------------------------------------------------------------
update t2 set t2.name='a' where t2.id=1;
update t2 set t2.name='b' where t2.id=2;
update t2 set t2.name='c' where t2.id=3;
update t2 set t2.name='d' where t2.id=4;
update t2 set t2.name='e' where t2.id=5;
update t2 set t2.name='f' where t2.id=6;
update t2 set t2.name='g' where t2.id=7;
7 rows selected.
SQL> update t2 set t2.name='a' where t2.id=1;
1 row updated.
SQL> update t2 set t2.name='b' where t2.id=2;
1 row updated.
SQL> update t2 set t2.name='c' where t2.id=3;
1 row updated.
SQL> update t2 set t2.name='d' where t2.id=4;
1 row updated.
SQL> update t2 set t2.name='e' where t2.id=5;
1 row updated.
SQL> update t2 set t2.name='f' where t2.id=6;
1 row updated.
SQL> update t2 set t2.name='g' where t2.id=7;
0 rows updated.
SQL> commit;

2、一条UPDATE 解决方案

1）、UPDATE 1个字段值

SQL> update t1 a -- 使用别名
  2  set name =(select b.name from t2 b where b.id=a.id)
  3  where exists (select 1 from t2 b where b.id=a.id);
6 rows updated.
SQL> select * from t1;
        ID NAME
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F
         7 g -- 未匹配到未更新
7 rows selected.
注：上面的 where exists 的语句是不能省略的，否则会导致一些没有匹配的行会被更新成null值。
SQL> update t1 a set name =(select b.name from t2 b where b.id=a.id);
7 rows updated.
SQL> select * from t1;
        ID NAME
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F
         7   -- 未匹配到更新为NULL
7 rows selected.

2）、UPDATE 多个字段值

 update t1 a -- 使用别名
 set （id,name) =(select b.id,b.name from t2 b where b.id=a.id)
 where exists (select 1 from t2 b where b.id=a.id);

3、MERGE (复杂且更新多值)

SQL> MERGE INTO t1 a 
  2  USING t2 b
  3  ON ( a.id = b.id )   
  4  WHEN MATCHED THEN UPDATE SET a.name = b.name 
  5  /
6 rows merged.
SQL> select * from t1;
        ID NAME
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F
         7 g
7 rows selected.

实现子查询更新

MERGE INTO customers  a
USING （select city_name,customer_id from cust_city ）b
ON ( a.id = b.customer_id )    
WHEN MATCHED THEN UPDATE SET a.cityE=b.city_name

注意：以上使用总结是根据个人经验来的，仅供参考。