有两个用户下都有同样一个表,现在要从一个用户的一条记录更新另一个用户一条记录.
用户1 nc55
用户2 nc56
create table demo
(
pk varchar2(20),
first varchar2(100)
)
insert into nc55.demo(pk,first) values('pk1','nick');
insert into nc56.demo(pk,first) values('pk1','greek');
现在要从nc55 下面的pk为pk1的记录更新nc56下面的pk为pk2的记录
更新结束后nc56也变成
pk 'pk1'
first 'nick'
也就是变成一模一样的.
用户1 nc55
用户2 nc56
create table demo
(
pk varchar2(20),
first varchar2(100)
)
insert into nc55.demo(pk,first) values('pk1','nick');
insert into nc56.demo(pk,first) values('pk1','greek');
现在要从nc55 下面的pk为pk1的记录更新nc56下面的pk为pk2的记录
更新结束后nc56也变成
pk 'pk1'
first 'nick'
也就是变成一模一样的.
执行
grant select on 用户1.nc55 to 用户2;2. 登入用户2,开始更新
update 用户2.nc56 a
set first=(select first
from 用户1.nc55 b
where pk=a.pk)
where a.pk in(select pk from 用户1.nc55);
where col1=a.col1)
where exists(select 1 from nc55.demo where col1=a.col1)如果字段名有规律,语句可以循环拼
where col1=a.col1)
where exists(select 1 from nc55.demo where col1=a.col1)
jhsj varchar2(14);
kssj varchar2(14);
jssj varchar2(14);
n number;
type refmycur is ref cursor;
mycur refmycur;
C1_NO demo%rowtype;
begin
begin
open mycur for select s.* from demo s where s.pk='pk1';
loop
fetch mycur into C1_NO;
exit when mycur%notfound;
n:=null;
select count(*) into n from nc56.demo where pk= C1_NO.pk ;
if n< 1 then
update nc56.demo a set 。 ;
commit;
end if;
end loop;
end;
end;