oracle dlink update,ORACLE关连更新updateselect

日月明王 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_timefrom (select vin,in_mileage,start_time ,r

日月明王

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

union

select vin,in_mileage,start_time from tt_ro_balanced)

group by vin,to_char(start_time,'yyyymmdd')

)

)

where wwmnum<3

) group by vin having max(start_time)-min(start_time)<>0 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 Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL> select * from wwm2; --要更新的表

TOWN ID

-------------------- ----------

222 222

111 111

ww'jj 111

llll 1111

dddd 2222

lllldf 111

lllldf 111

dsafdf 111

3435 111

ljjjjj 222

dsafdf 111

TOWN ID

-------------------- ----------

3435 111

ljjjjj 222

SQL> select * from wwm5; --更新的条件表

TOWN ID

-------------------- ----------

lllldf 111

test 9984

SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id

2 /

TOWN ID

-------------------- ----------

111 111

ww'jj 111

lllldf 111

lllldf 111

dsafdf 111

3435 111

dsafdf 111

3435 111

8 rows selected.

所以,每次需要更新8条数据就是正确的.

相信程序员是通过以下类似的SQL更新的,这是错误的,因为没有加WHERE

SQL> 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

-------------------- ----------

222

lllldf 111

lllldf 111

1111

2222

lllldf 111

lllldf 111

lllldf 111

lllldf 111

222

lllldf 111

TOWN ID

-------------------- ----------

lllldf 111

222

13 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=btown

SQL> /

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 222

lllldf 111

lllldf 111

llll 1111

dddd 2222

lllldf 111

lllldf 111

lllldf 111

lllldf 111

ljjjjj 222

lllldf 111

TOWN ID

-------------------- ----------

lllldf 111

ljjjjj 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 MATCHED

222 222

lllldf 111

lllldf 111

llll 1111

dddd 2222

lllldf 111

lllldf 111

lllldf 111

lllldf 111

ljjjjj 222

TOWN ID

-------------------- ----------

lllldf 111

lllldf 111

ljjjjj 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) b

SQL> 3 on (wwm2.id=b.id)

SQL> 4 when matched then update set town=b.town

SQL> 5* when not matched then insert (town,id) values (null,null)

SQL> /

8 rows merged.

以上就是5种关连更新的例子了,希望能给开发人员解惑. 本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网