等值更新的两种方式

方式一：使用游标的方式

declare
  v_jobcode%hr_post_info.jobcode          ;
  v_KEYPOSTSFLAG%hr_post_info.keypostsflag;
  v_KEYPOSTSDESC%hr_post_info.keypostsdesc;
  --v_khnumber mt_kh.khnumber%type;
  CURSOR cur_hr_post_info is
    select KEYPOSTSFLAG, KEYPOSTSDESC, jobcode
      from hr_post_info@db107_link;
begin

  open cur_hr_post_info;
  loop
    fetch cur_length
      into v_jobcode, v_KEYPOSTSDESC, v_KEYPOSTSFLAG;
    EXIT WHEN cur_hr_post_info%NOTFOUND;
    update cur_hr_post_info
       set KEYPOSTSDESC = V_KEYPOSTSDESC, KEYPOSTSFLAG = v_KEYPOSTSFLAG
     where jobcode = v_jobcode;
    commit;
  end;

 

方式二：使用子查询的方式

 

update min a
   set name =
       (select name from min@db107_link b where b.id = a.id)
 where exists (select 1 from min@db107_link b where b.id = a.id);

 

注：此时id必须为主键！