[转]oracle update set select from 关联更新
2024-09-03 21:01:33
本文转自:http://blog.csdn.net/disiwei1012/article/details/52589181
http://www.blogjava.net/Jhonney/archive/2010/06/25/324503.html
$ sqlplus user/pass SQL*Plus: Release 9.2.0.6. - Production on Wed Aug :: Copyright (c) , , Oracle Corporation. All rights reserved. Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6. - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6. - Production SQL> select * from wwm2; --要更新的表 TOWN ID
-------------------- ---------- ww'jj 111
llll
dddd
lllldf
lllldf
dsafdf ljjjjj
dsafdf TOWN ID
-------------------- ---------- ljjjjj SQL> select * from wwm5; --更新的条件表 TOWN ID
-------------------- ----------
lllldf
test SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id
/ TOWN ID
-------------------- ---------- ww'jj 111
lllldf
lllldf
dsafdf dsafdf rows selected. 所以,每次需要更新8条数据就是正确的. 相信程序员是通过以下类似的SQL更新的,这是错误的,因为没有加WHERE
SQL> update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id)
/ rows updated. SQL> select * from wwm2; TOWN ID
-------------------- ---------- lllldf
lllldf lllldf
lllldf
lllldf
lllldf lllldf TOWN ID
-------------------- ----------
lllldf rows selected. 可以看到13条记录被更新,符合条件的更新正确,不符合条件的也更新为NULL.以下是正确的方法 方法一:
SQL> update wwm2
set town=(select town from wwm5 where wwm5.id=wwm2.id)
where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id)
/ rows updated. 方法二: 与方法一道理相同,这里需要掌握EXIST的相关用法.
SQL> update wwm2
set town=(select town from wwm5 where wwm5.id=wwm2.id)
where exists (select from wwm5 where wwm5.id=wwm2.id)
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)
set atown=btown
/
set atown=btown
*
ERROR at line :
ORA-: cannot modify a column which maps to a non key-preserved table * alter table wwm5 add primary key (id)
SQL> / Table altered. update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id)
* set atown=btown
SQL> / 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
-------------------- ---------- lllldf
lllldf
llll
dddd
lllldf
lllldf
lllldf
lllldf
ljjjjj
lllldf TOWN ID
-------------------- ----------
lllldf
ljjjjj 这个方法是最灵活的了. 方法五: 注意,方法五只能适用于WWM5是WWM2的子集的时候.
merge into wwm2
using (select town,id from wwm5) b
on (wwm2.id=b.id)
when matched then update set town=b.town
* when not matched then insert (town,id) values (null,null)
SQL> / 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=,这样一来就不会执行WHEN NOT MATCHED lllldf
lllldf
llll
dddd
lllldf
lllldf
lllldf
lllldf
ljjjjj TOWN ID
-------------------- ----------
lllldf
lllldf
ljjjjj rows selected. SQL> delete from wwm5 where id=; row deleted. SQL> merge into wwm2
SQL> using (select town,id from wwm5) b
SQL> on (wwm2.id=b.id)
SQL> when matched then update set town=b.town
SQL> * when not matched then insert (town,id) values (null,null)
SQL> / rows merged. 以上就是5种关连更新的例子了,希望能给开发人员解惑. 说明:如果select 子句可以返回多行记录,但返回适合where条件的记录只能是唯一的,否则将会报返回单行的select子句返回多行的错误,因为update只能跟据此处的where子句(内层where)进行相应记录的匹配更新,一次只能是一条。
最新文章
- Orcle学习(一)
- poj1144
- asp中cookie欺骗/注入原理与防范
- CNAME
- iOS 中的UIWindow
- Problem A+B
- Ubuntu输入密码之后,桌面闪一下黑屏,然后又返回到输入密码界面。但是其他账户可以登入
- 用jQuery的ajax请求一般处理程序返回json数据
- 网​址​U​R​L​中​特​殊​字​符​转​义​编​码
- selenium3.7+ python3 添加cookie模拟登陆
- ajax请求返回乱码
- lambda表达式初步
- 版本控制工具——Git常用操作(上)
- vue_小项目_吃饭睡觉打豆豆
- git如何创建 .gitignore文件
- Nginx+Redis+Ehcache大型高并发高可用三层架构总结
- django----用户认证(auth模块)
- es6的解构赋值用途
- [HDOJ]Coin Change(DP)
- iOS8 之后 tableview separatorInset cell分割线左对齐,ios7的方法失效了