Oracle批量更新

①导出批量更新SQL

select CONCAT(CONCAT(CONCAT(CONCAT('update test_a set a_date=', b_date),
                            'where a_id='),
                     b_id),
              ' and a_flag=1;')
  from test_b;

②批量更新数据

#方法一  
update test_a a
   set a.a_date = (select b.b_date
                     from test_b b
                    where a.a_id = b.b_id
                      and a.a_flag = 1);
 
#方法二
begin
  for r in (select a.rowid, b.b_date
              from TEST_A a, test_b b
             where a.a_id = b.b_id
             and a.a_flag =1) loop
    update TEST_A t set t.a_date = r.b_date where t.rowid = r.rowid;
  end loop;
end;


#方法三
create or replace procedure test as
  type temp_record IS record(b_id test_b.b_id%type,
  b_date test_b.b_date%type
  );
  type temp_record_table IS table OF temp_record;
  tmp temp_record_table ;
begin
  SELECT b.b_id, b.b_date BULK COLLECT INTO tmp FROM test_b b;
  FOR I IN 1 .. tmp.COUNT LOOP
    UPDATE TEST_A a SET a.a_date = tmp(I).b_date
    WHERE a.a_id = tmp(I).b_id
         AND a.a_flag = 1;
  END LOOP;
end;


#方法四
merge into TEST_A a
using test_b b
on (a.a_id = b.b_id and a.a_flag = 1)
when matched then
  update set a.a_date = b.b_date;