表对表数据更新

Posted by     小兵兵 on Thursday, December 10, 2020

目录

工作中经常会涉及到表对表更新,即根据一个表的数据更新另一个表中的一部分数据。

两个千万级别的大表中,实现百万级的数据更新时

常用的方法有如下几种

游标

效率慢,不推荐

BEGIN
  FOR cr IN (SELECT a.rowid, b.join_state
               FROM t_join_situation a, t_people_info b
              WHERE a.people_number = b.people_number
                AND a.year = '2011'
                AND a.city_number = 'M00000'
                AND a.town_number = ‘M51000’) LOOP
    UPDATE t_join_situation
       SET join_state = cr.join_state
     WHERE ROWID = cr.rowid;
  END LOOP;
END;

表对表update

UPDATE t_join_situation a
   SET a.join_state =
       (SELECT b.join_state
          FROM t_people_info b
         WHERE a.people_number = b.people_number
           AND a.year = '2011'
           AND a.city_number = 'M00000'
           AND a.town_number = 'M51000')
 WHERE EXISTS (SELECT 1
          FROM t_people_info b
         WHERE a.people_number = b.people_number
           AND a.year = '2011'
           AND a.city_number = 'M00000'
           AND a.town_number = 'M51000');

inline view

两表关联且被更新表通过关联表主键关联的,采用此方案更优

UPDATE (SELECT a.join_state asjoin_state_a, b.join_state AS join_state_b
          FROM t_join_situation a, t_people_info b
         WHERE a.people_number = b.people_number
           AND a.year = '2011'
           AND a.city_number = 'M00000'
           AND a.town_number = 'M51000')
   SET join_state_a = join_state_b;

merge into

效率高,推荐

有则更新,无则插入

两表关联且被更新表不是通过关联表主键关联的,采用此方案更优

MERGE INTO table_name alias1
USING (TABLE | view | sub_query) alias2
ON (JOIN condition)
WHEN MATCHED THEN
  UPDATE table_name SET alias1.col1 = alias2.col_val1, alias1.col2 = alias2.col2_val
WHEN NOT MATCHED THEN
  INSERT (alias1.column_list) VALUES (alias2.column_values);

参考

Oracle的update语句优化研究