Batch update data

Today, developers want to update a batch of data, and it has not been updated for a long time. After finding me, I found the information on the Internet and found that the rowid method is the fastest updated. refer to the following article to record the update.

Dave article: http://blog.csdn.net/tianlesoftware/article/details/6576156

 

Original SQL:

update t_canhe_member_info a set a.id_card = (select b.id_card from BBB_EEE b                                                      where a.member_no = b.member_no)                                   where a.join_year = ‘2014‘ ;

 

Batch update of PL/SQL blocks:

Declare cursor cur is select. id_card, B. rowid row_id from bbb_eee A, t_canhe_member_info B where. member_no = B. member_no and B. join_year = '000000' order by B. rowid; --- if the table data volume is not large, you do not need to use order by rowid v_counter number; v_jishu number; -- count the number of updates

Begin v_counter: = 0; v_jishu: = 0; for row in cur loop update t_canhe_member_info set id_card = row. id_card where rowid = row. row_id; v_counter: = v_counter + 1; v_jishu: = v_jishu + 1; if (v_counter> = 1000) then commit; -- submit v_counter: = 0; end if; end loop; commit; dbms_output.put_line ('updated '| v_jishu |! '); End;

Batch update data