Oracle⾥⾯updateselect的⽅法
⽅法⼀:
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 (wn atown,a.id wn 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 (wn atown,a.id wn btown,b.id bid from wwm
2 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=wn
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 wn
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
sql中select是什么意思
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 wn
SQL> 5* when not matched then insert (town,id) values (null,null)
SQL> /
8 rows merged.
以上就是5种关连更新的例⼦了，希望能给开发⼈员解惑.
说明：如果select ⼦句可以返回多⾏记录，但返回适合where条件的记录只能是唯⼀的，否则将会报返回单⾏的select⼦句返回多⾏的错误，因为update只能跟据此处的where⼦句（内层where)进⾏相应记录的匹配更新，⼀次只能是⼀条。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。