declare cursor c2 is select column_name from user_table where table_name='M'; cursor c3 is select column_name from user_table where table_name='N'; begin for v2 in c2 loop for v3 in c3 loop if v2.column_name=v3.column_name then str:='update m set '||c2.column_name||'=(select '||c3.column_name||' from n where n.c=m.c and n.d=x) where m.d=y'; execute immediate str; end if; end loop; end loop; end; /
UPDATE m SET (a,b) = (select a,b from n WHERE n.c = m.c and n.d = x ) where m.d = y
UPDATE m SET (a,b) = (select a,b from n WHERE n.c = m.c and n.d = x ) where m.d = y and exists(select 1 from n WHERE n.c = m.c and n.d = x )
m.a = (select a from n WHERE m.c = n.c and n.d = x and m.d = y ) ,
m.b = (select b from n WHERE m.c = n.c and n.d = x and m.d = y )
我想用cursor取出数据,再一条一条update,
还有更好的方法吗?
cursor c2 is
select column_name from user_table where table_name='M';
cursor c3 is
select column_name from user_table where table_name='N';
begin
for v2 in c2 loop
for v3 in c3 loop
if v2.column_name=v3.column_name then
str:='update m set '||c2.column_name||'=(select '||c3.column_name||' from n where n.c=m.c and n.d=x) where m.d=y';
execute immediate str;
end if;
end loop;
end loop;
end;
/
where m.d = y
where m.d = y
and exists(select 1 from n WHERE n.c = m.c and n.d = x )