日月明王http://sunmoonking.spaces.live.com今天写了个复杂的sql,用来更新另一个表
select vin,(max(in_mileage)-min(in_mileage))/(max(start_time)-min(start_time)) from ( select vin,in_mileage,start_time from (select vin,in_mileage,start_time , row_number() over (partition by vin order by start_time) wwmnum from(select vin,max(in_mileage) in_mileage,max(start_time) start_time from (select vin,in_mileage,start_time from tt_repair_order unionselect vin,in_mileage,start_time from tt_ro_balanced) group by vin,to_char(start_time,'yyyymmdd') )) where wwmnum0 and to_char(max(start_time),'yyyymmdd')to_char(min(start_time),'yyyymmdd')
写完这个sql交给程序员后,程序员跟我说不会update,也就是说通过b表更新a表程序员是有困难的,于是在这里整理下各种不同的方法以供以后使用.
$ sqlplus user/pass
sql*plus: release 9.2.0.6.0 - production on wed aug 2 17:38:39 2006
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to:oracle9i enterprise edition release 9.2.0.6.0 - 64bit productionwith the partitioning, olap and oracle data mining optionsjserver release 9.2.0.6.0 - production
sql> select * from wwm2; --要更新的表
town id-------------------- ----------222 222111 111ww'jj 111llll 1111dddd 2222lllldf 111lllldf 111dsafdf 1113435 111ljjjjj 222dsafdf 111
town id-------------------- ----------3435 111ljjjjj 222
sql> select * from wwm5; --更新的条件表
town id-------------------- ----------lllldf 111test 9984
sql> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id 2 /
town id-------------------- ----------111 111ww'jj 111lllldf 111lllldf 111dsafdf 1113435 111dsafdf 1113435 111
8 rows selected.
所以,每次需要更新8条数据就是正确的.
相信程序员是通过以下类似的sql更新的,这是错误的,因为没有加wheresql> update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id) 2 /
13 rows updated.
sql> select * from wwm2;
town id-------------------- ---------- 222lllldf 111lllldf 111 1111 2222lllldf 111lllldf 111lllldf 111lllldf 111 222lllldf 111
town id-------------------- ----------lllldf 111 22213 rows selected.
可以看到13条记录被更新,符合条件的更新正确,不符合条件的也更新为null.以下是正确的方法
方法一:sql> update wwm2 2 set town=(select town from wwm5 where wwm5.id=wwm2.id) 3 where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id) 4 /
8 rows updated.
方法二: 与方法一道理相同,这里需要掌握exist的相关用法.sql> update wwm2 set town=(select town from wwm5 where wwm5.id=wwm2.id) where exists (select 1 from wwm5 where wwm5.id=wwm2.id)8 rows updated.
方法三:sql> update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) 2 set atown=btown 3 /set atown=btown *error at line 2:ora-01779: cannot modify a column which maps to a non key-preserved table
1* alter table wwm5 add primary key (id)sql> /
table altered.
1 update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) 2* set atown=btownsql> /
8 rows updated.
这种方法的局限性就是需要primary 的支持.
方法四: 1 declare 2 cursor cur_wwm is select town,id from wwm5; 3 begin 4 for my_wwm in cur_wwm loop 5 update wwm2 set town=my_wwm.town 6 where id=my_wwm.id; 7 end loop; 8* end;sql> /
pl/sql procedure successfully completed.
sql> select * from wwm2;
town id-------------------- ----------222 222lllldf 111lllldf 111llll 1111dddd 2222lllldf 111lllldf 111lllldf 111lllldf 111ljjjjj 222lllldf 111
town id-------------------- ----------lllldf 111ljjjjj 222
这个方法是最灵活的了.
方法五:
注意,方法五只能适用于wwm5是wwm2的子集的时候. 1 merge into wwm2 2 using (select town,id from wwm5) b 3 on (wwm2.id=b.id) 4 when matched then update set town=b.town 5* when not matched then insert (town,id) values (null,null)sql> /
9 rows merged.
sql> select * from wwm2;
town id-------------------- ---------- ---注意这个地方,被插入了一个空值.因为wwm5的id=9984在wwm2中不能匹配,根本原因是oracle9必须有when not matched子句,但是oracle10可以不许要,也就是oracle10可以不写when not matched ,就不必插入null值了,为解决这个问题,下一步会delete wwm5的id=9984,这样一来就不会执行when not matched222 222lllldf 111lllldf 111llll 1111dddd 2222lllldf 111lllldf 111lllldf 111lllldf 111ljjjjj 222
town id-------------------- ----------lllldf 111lllldf 111ljjjjj 222
14 rows selected.
sql> delete from wwm5 where id=9984;
1 row deleted.
sql> 1 merge into wwm2 sql> 2 using (select town,id from wwm5) bsql> 3 on (wwm2.id=b.id)sql> 4 when matched then update set town=b.townsql> 5* when not matched then insert (town,id) values (null,null)sql> /
8 rows merged.
以上就是5种关连更新的例子了,希望能给开发人员解惑.