update实现select多行插入

1.环境准备

sys@ORCL>create table zhong(id int);

sys@ORCL>create table shall(id int,test int);

 

sys@ORCL>insert into zhong values(1);

sys@ORCL>insert into zhong values(3);

sys@ORCL>insert into zhong values(5);

sys@ORCL>insert into zhong values(7);

sys@ORCL>insert into shall values(1,1000);

sys@ORCL>insert into shall values(2,5000);

sys@ORCL>insert into shall values(3,500);

sys@ORCL>insert into shall values(5,999);

sys@ORCL>commit;

 

sys@ORCL>select * from zhong;

        ID

----------

         1

         3

         5

         7

 

sys@ORCL>select * from shall;

        ID       TEST

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

         1       1000

         2       5000

         3        500

         5        999

 

2.使用游标完成

sys@ORCL>select b.test from shall b left join zhong a on b.id=a.id;

      TEST

----------

      1000

       500

       999

      5000

 

sys@ORCL>update zhong a set a.id=(select b.test from shall b left join zhong a on b.id=a.id);

update zhong a set a.id=(select b.test from shall b left join zhong a on b.id=a.id)

                         *

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

 

------处理方法：

 

begin

 for row_rec in (select b.* from shall b left join zhong a on b.id=a.id) loop

  update zhong a set a.id=row_rec.test where a.id=row_rec.id;

 end loop;

 commit;

end;

/

PL/SQL procedure successfully completed.

 

sys@ORCL>select * from zhong;

        ID

----------

      1000

       500

       999

         7

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2121990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2121990/