oracle 批量更新之将一个表的数据批量更新至另一个表

时间:2022-01-16 12:04:24
 

oracle 批量更新之将一个表的数据批量更新至另一个表

CreationTime--2018年7月3日17点38分

Author:Marydon

Oracle 将一个表的指定字段的值更新至另一个表的对应字段

案例一:

1.情景描述

  testdata表数据展示

oracle 批量更新之将一个表的数据批量更新至另一个表

  testdata2表数据展示

oracle 批量更新之将一个表的数据批量更新至另一个表

  数据对比:

  testdata表有31条数据,且有9条数据的userid与testdata2表不一致(自己独有);

  testdata2表有24条数据,且有2条数据的userid与testdata2表不一致(自己独有)。

  表关联:testdata表和testdata2表的userid具有关联关系

  需求说明:

  需要将testdata中的22条数据更新到testdata2表中  

2.错误方式

  这种方式,由于没有设置更新的限制条件,导致:

  不仅会更新需要更新的表记录,还会将testdata2中剩余的数据字段更新为空。

  oracle 批量更新之将一个表的数据批量更新至另一个表

3.解决方案

  添加where条件,只对两表共有的数据进行更新。

  2018/12/05

  正确格式:

UPDATE TABLE1 T1
SET (T1.COLUMN1, T1.COLUMN2) =
(SELECT T2.COLUMN1, T2.COLUMN2 FROM TABLE2 T2 WHERE T2.ID = T1.ID) --两表能够进行关联的字段(一般是主键)
WHERE EXISTS (SELECT 1 FROM TABLE2 T2 WHERE T2.ID = T1.ID);

  错误格式:

UPDATE TABLE1 T1
SET (T1.COLUMN1, T1.COLUMN2) =
(SELECT T2.COLUMN1, T2.COLUMN2 FROM TABLE2 T2 WHERE T2.ID = T1.ID)
WHERE EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.ID = T2.ID);--exists()访问不到t2表

  说明:虽然理论上,它们执行效果是一样的,但是由于exists()函数访问不到t2表,所以查询访问不到的那张表t2。

  套用

--添加限制条件,进行更新
update testdata2 t2
set (t2.usercode, t2.userpassword) =
(select t.usercode, t.userpassword
from testdata t
where t.userid = t2.userid)
where exists (select 1 from testdata t where t.userid = t2.userid);

oracle 批量更新之将一个表的数据批量更新至另一个表

  避免了全表更新。

  2018/12/07

案例二:

4.索引的重要性

  更新1w条以上的数据时,where后面跟的条件一定要加上索引,不然哭都来不及。

  需要更新22w条数据,两张表的ID_CARD字段没有建索引

oracle 批量更新之将一个表的数据批量更新至另一个表

   两张表的ID_CARD字段建立索引后

oracle 批量更新之将一个表的数据批量更新至另一个表

  这是我的亲身经历,没有建索引前,执行了4个小时,没有更新完,后来由于电脑关机,导致任务终止;

  建索引后,同样执行该sql,更新22w条数据只用了4秒!!!  

说明:对于要执行的更新sql,使用plsql的预测功能时预测不准确,只做参考。

2019/01/10

案例三:

  将VIRTUAL_CARD表中的ADDRESS字段数据更新至TEST_CARD表中的ADDRESS字段

  方式一:sql实现

UPDATE TEST_CARD T
SET T.ADDRESS =
(SELECT T2.ADDRESS FROM VIRTUAL_CARD T2 WHERE T.CARDNUM = T2.CARDNUM)
WHERE EXISTS (SELECT 1 FROM VIRTUAL_CARD T2 WHERE T2.CARDNUM = T.CARDNUM)

  方式二:借助plsql工具

  将要更新的表的rowid查询出来,并获取最后页

oracle 批量更新之将一个表的数据批量更新至另一个表

  编辑数据--》选中Address字段所有数据

oracle 批量更新之将一个表的数据批量更新至另一个表

  右键--》复制--》选中第一列的Address所有数据--》粘贴

oracle 批量更新之将一个表的数据批量更新至另一个表

  勾选上图绿色勾,提交即可。

oracle 批量更新之将一个表的数据批量更新至另一个表

  说明:

  大批量修改数据时,推荐使用方式一更新;

  少量数据修改时,两种方式均可。

5.总结

  A表中有几个字段的值需要批量更新,如果一个一个进行修改,太慢了:

  将批量修改后的内容放到一个新的表中B,并与要更新的表A建立关联关系。

  第一步:建新表

  两表关联字段(column1):A表中该字段必须具有唯一性;

  要更新的字段(column5,cloumn6,...)。

  第二步:导数据

  将新表字段及每条记录更新后内容放到excel中,导入oracle数据库中B表。

  第三步:根据两表关联关系,将B表数据批量更新到A表中。