oracle update set select from 关联更新

来源：互联网发布：稻谷出糙率计算数据编辑：程序博客网时间：2024/05/17 22:27

SQL>select * from wwn2;TOWN                         ID-------------------- ----------222                         222111                         111ww'jj                       111llll                       1111dddd                       2222lllldf                      111lllldf                      111dsafdf                      1113435                        111ljjjjj                      222dsafdf                      1113435                        111ljjjjj                      222SQL> select * from wwm5;          TOWN                         ID-------------------- ----------lllldf                      111test                       9984SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.idTOWN                         ID-------------------- ----------111                         111ww'jj                       111lllldf                      111lllldf                      111dsafdf                      1113435                        111dsafdf                      1113435                        1118 rows selected.--需要更新8条数据是正确的下面是一个错误的做法：SQL>  update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id)13 rows updated.SQL> select * from wwm2;TOWN                         ID-------------------- ----------                            222lllldf                      111lllldf                      111                           1111                           2222lllldf                      111lllldf                      111lllldf                      111lllldf                      111                            222lllldf                      111lllldf                      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)

方法二：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)

方法三：  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;

说明：如果select 子句可以返回多行记录，但返回适合where条件的记录只能是唯一的，否则将会报返回单行的select子句返回多行的错误，因为update只能跟据此处的where子句（内层where)进行相应记录的匹配更新，一次只能是一条。

原文点这里

0 0