两表关联更新 oracle mssql

• sqlserver支持update from的语法

        其实a和b做了一次内连接； 

MSSQL：

update A  
 set A.id=B.id 
 from   A,  B
where  A.name=B.name

而oracle不支持update from的语法，那再来看看oracle的写法：

Oracle：

update a set a.id=  (select  b.id from b where a.NAME = b.NAME)  where exists (select 1 from b where  A.name=B.name) --没有where 不匹配的更新为空了

drop table t1;
drop table t2;
CREATE TABLE T1 
(
  name VARCHAR2(10) 
, code VARCHAR2(10) 
);
ALTER TABLE t1 ADD PRIMARY KEY(name);
insert into t1 (name,code) values('lw01','01');
insert into t1 (name,code) values('lw02','02');
 
CREATE TABLE T2
(
  name VARCHAR2(10) 
, code VARCHAR2(10) 
);
ALTER TABLE t2 ADD PRIMARY KEY(name);
insert into t2 (name,code) values('lw03','03');
insert into t2 (name,code) values('lw02','lw02');
insert into t2 (name,code) values('lw01','lw01');

需求：
参照T2表，修改T1表，修改条件为两表的name列内容一致。

方式1：update
UPDATE T1 t1
   SET t1.code =
       (select t2.code from T2 t2 where t2.name = t1.name)
 WHERE EXISTS (SELECT 1 FROM T2 t2 WHERE t2.name = t1.name);

如果同时更新多个字段可以参照以下语法： 

UPDATE DEMO_T1 t1
SET (字段一,字段二,...) = (select 字段一,字段二,... from DEMO_T2 T2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM DEMO_T2 T2 WHERE T2.FNAME = T1.FNAME);
方式2：内联视图更新
注意：需要取数据的表，该字段必是主键或者有唯一约束

UPDATE (select t1.code code1, t2.code code2
          from t1 t1, t2 t2
         where t1.name = t2.name) t
   set code1 = code2;
 

 方式3：merge
 

 

为了方便起见,建立了以下简单模型,和构造了部分测试数据: 

在某个业务受理子系统BSS中， 
--客户资料表 
create table customers 
( 
customer_id number(8) not null, -- 客户标示 
city_name varchar2(10) not null, -- 所在城市 
customer_type char(2) not null, -- 客户类型 

... 
) 
create unique index PK_customers on customers (customer_id) 
由于某些原因，客户所在城市这个信息并不什么准确，但是在 
客户服务部的CRM子系统中，通过主动服务获取了部分客户20%的所在 
城市等准确信息，于是你将该部分信息提取至一张临时表中： 
create table tmp_cust_city 
( 
customer_id number(8) not null, 
citye_name varchar2(10) not null， 
customer_type char(2) not null 
) 
1) 最简单的形式 
--经确认customers表中所有customer_id小于1000均为'北京' 
--1000以内的均是公司走向全国之前的本城市的老客户:) 
update customers 
set city_name='北京' 
where customer_id<1000 
2) 两表(多表)关联update -- 仅在where字句中的连接 
--这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别 
update customers a -- 使用别名 
set customer_type='01' --01 为vip，00为普通 
where exists (select 1 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
3) 两表(多表)关联update -- 被修改值由另一个表运算而来 
update customers a -- 使用别名 
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) 
where exists (select 1 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
-- update 超过2个值 
update customers a -- 使用别名 
set (city_name,customer_type)=(select b.city_name,b.customer_type 
from tmp_cust_city b 
where b.customer_id=a.customer_id) 
where exists (select 1 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
注意在这个语句中， 
=(select b.city_name,b.customer_type 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
与 
(select 1 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
是两个独立的子查询，查看执行计划可知，对b表/索引扫描了2篇； 
如果舍弃where条件，则默认对A表进行全表 
更新，但由于(select b.city_name from tmp_cust_city b where 
where b.customer_id=a.customer_id) 
有可能不能提供"足够多"值，因为tmp_cust_city只是一部分客户的信息， 
所以报错（如果指定的列--city_name可以为NULL则另当别论）： 

01407, 00000, "cannot update (%s) to NULL" 
// *Cause: 
// *Action: 
一个替代的方法可以采用： 
update customers a -- 使用别名 
set city_name=nvl((select b.city_name from tmp_cust_city b 
where b.customer_id=a.customer_id),a.city_name) 
或者 
set city_name=nvl((select b.city_name from tmp_cust_city b 
where b.customer_id=a.customer_id),'未知') 
-- 当然这不符合业务逻辑了 
4) 上述3)在一些情况下，因为B表的纪录只有A表的20-30%的纪录数， 
考虑A表使用INDEX的情况，使用cursor也许会比关联update带来更好的性能： 

set serveroutput on 
declare 
cursor city_cur is 
select customer_id,city_name 
from tmp_cust_city 
order by customer_id; 
begin 
for my_cur in city_cur loop 
update customers 
set city_name=my_cur.city_name 
where customer_id=my_cur.customer_id; 

/** 此处也可以单条/分批次提交，避免锁表情况 **/ 
-- if mod(city_cur%rowcount,10000)=0 then 
-- dbms_output.put_line('----'); 
-- commit; 
-- end if; 
end loop; 
end; 
5) 关联update的一个特例以及性能再探讨 
在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例： 
update (select a.city_name,b.city_name as new_name 
from customers a, 
tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
set city_name=new_name 
这样能避免对B表或其索引的2次扫描，但前提是 A(customer_id) b(customer_id)必需是unique index 
或primary key。否则报错： 

01779, 00000, "cannot modify a column which maps to a non key-preserved table" 
// *Cause: An attempt was made to insert or update columns of a join view which 
// map to a non-key-preserved table. 
// *Action: Modify the underlying base tables directly. 
6）oracle另一个常见错误 
回到3)情况,由于某些原因，tmp_cust_city customer_id 不是唯一index/primary key 
update customers a -- 使用别名 
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) 
where exists (select 1 
from tmp_cust_city b 
where b.customer_id=a.customer_id 
) 
当对于一个给定的a.customer_id 
(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) 
返回多余1条的情况，则会报如下错误： 

01427, 00000, "single-row subquery returns more than one row" 
// *Cause: 
// *Action: 
一个比较简单近似于不负责任的做法是 
update customers a -- 使用别名 
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) 

一个比较简单近似于不负责任的做法是 
update customers a -- 使用别名 
set city_name=(select b.city_name from tmp_cust_city b 
where b.customer_id= 
a.customer_id and rownum=1) 

如何理解 01427 错误，在一个很复杂的多表连接update的语句，经常因考虑不周，出现这个错误， 
仍已上述例子来描述，一个比较简便的方法就是将A表代入 值表达式 中,使用group by 和 
having 字句查看重复的纪录 
(select b.customer_id,b.city_name,count(*) 
from tmp_cust_city b,customers a 
where b.customer_id=a.customer_id 
group by b.customer_id,b.city_name 
having count(*)>=2 
)