create table R( id varchar2(50),dz varchar2(50)) ;
delete from R
insert into R values ('1111','11112');
insert into R values ('2222','22223');
insert into R values ('3333','33335');
insert into R values ('4444','44443');
insert into R values ('5555','55552');create table F( id varchar2(50),dz varchar2(50));
delete from F
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('2222','0');
insert into F values ('2222','0');
insert into F values ('3333','0');
insert into F values ('3333','0');
insert into F values ('4444','0');
insert into F values ('5555','0');现想通过查询R表的dz字段,修改F表的dz字段,由于F表中的id字段不唯一,出现单行查询返回多行的问题
语句如下:
update F set dz=(select A.dz from R A,F b where A.id=B.id)
delete from R
insert into R values ('1111','11112');
insert into R values ('2222','22223');
insert into R values ('3333','33335');
insert into R values ('4444','44443');
insert into R values ('5555','55552');create table F( id varchar2(50),dz varchar2(50));
delete from F
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('2222','0');
insert into F values ('2222','0');
insert into F values ('3333','0');
insert into F values ('3333','0');
insert into F values ('4444','0');
insert into F values ('5555','0');现想通过查询R表的dz字段,修改F表的dz字段,由于F表中的id字段不唯一,出现单行查询返回多行的问题
语句如下:
update F set dz=(select A.dz from R A,F b where A.id=B.id)
update F B
set dz=(select A.dz from R A where A.id=B.id and rownum=1)
where exists(select 1 from R A where A.id=B.id )
create table R( id varchar2(8),dz varchar2(8));
insert into R values ('1111','11112');
insert into R values ('2222','22223');
insert into R values ('3333','33335');
insert into R values ('4444','44443');
insert into R values ('5555','55552');
create table F( id varchar2(8),dz varchar2(8));
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('1111','0');
insert into F values ('2222','0');
insert into F values ('2222','0');
insert into F values ('3333','0');
insert into F values ('3333','0');
insert into F values ('4444','0');
insert into F values ('5555','0');
--
update f
set f.dz=(
select r.dz
from r
where r.id=f.id)
where exists(
select 1 from r
where f.id=r.id)
SQL> select * from f;ID DZ
-------- --------
1111 11112
1111 11112
1111 11112
2222 22223
2222 22223
3333 33335
3333 33335
4444 44443
5555 55552