ORACLE使用批量更新来拆分段并更新到一个新的字段中

彦菘

已于 2024-06-08 11:03:49 修改

阅读量78

点赞数

文章标签： oracle 数据库 database

于 2022-01-25 10:56:23 首次发布

本文链接：https://blog.csdn.net/qq_22905801/article/details/122681097

版权

使用replace将字段中的某个符号去掉

 update LRZ_XX t set t.DESTINATIONSTREET=replace(t.DESTINATIONSTREET,'-','') where t.DESTINATIONSTREET like '%-%'

简单的方法

--省
update LRZ_XX   t set t.destinationpriv= SUBSTR (DESTINATION, 1, INSTR ( t.DESTINATION, '-', 1, 1) - 1)   
--市
update LRZ_XX   t set t.destinationcity= SUBSTR (DESTINATION, 
  INSTR (t.DESTINATION, '-', 1, 1) + 1,
   INSTR (t.DESTINATION, '-', 1, 2) - INSTR (t.DESTINATION, '-', 1, 1)-1)   
--详细地址
update LRZ_XX   t set t.DESTINATIONDETIAL= SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '-', 1, 2) + 1)  

--区地址
update LRZ_XX   t set t.DESTINATIONAREA= '开发区'   where  t.DESTINATION  like  '%开发%'
--街道

--1区下面的街道
select DESTINATION, SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '区', 1, 1) + 1,
   INSTR (t.DESTINATION, '街道', 1, 1) - INSTR (t.DESTINATION, '区', 1, 1)+1) from LRZ_XX t  where  t.DESTINATION  like '%街道%'
   and  t.DESTINATION  not  like  '%县%'   /*and t.DESTINATION  like '%山东省-日照市-东港区-两城街道孙家官庄村%'*/

--2区下面的镇
select DESTINATION, SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '区', 1, 1) + 1,
   INSTR (t.DESTINATION, '镇', 1, 1) - INSTR (t.DESTINATION, '区', 1, 1)) from LRZ_XX125 t  where  t.DESTINATION  like '%镇%'
   and  t.DESTINATION  not  like  '%街道%'
--3县下面的镇
select DESTINATION,
SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '县', 1, 1) + 1,
   INSTR (t.DESTINATION, '镇', 1, 1) - INSTR (t.DESTINATION, '县', 1, 1))  from LRZ_XX t where  t.DESTINATION  like '%县%'
   and  t.DESTINATION  not  like  '%街道%' 
--4县下面的街道
select DESTINATION,
SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '县', 1, 1) + 1,
   INSTR (t.DESTINATION, '街道', 1, 1) - INSTR (t.DESTINATION, '县', 1, 1)) from LRZ_XX125 t where  t.DESTINATION  like '%县%'
   and  t.DESTINATION  not  like  '%街道%'     where  t.DESTINATION  like '%县%'

--更新区下面的街道       
update LRZ_XX   t set t.DESTINATIONSTREET= SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '区', 1, 1) + 1,
   INSTR (t.DESTINATION, '街道', 1, 1) - INSTR (t.DESTINATION, '区', 1, 1)+1)  where  t.DESTINATION  like '%街道%'
   and  t.DESTINATION  not  like  '%县%' 

 
--更新县下面的镇 
update LRZ_XX   t set t.DESTINATIONSTREET= SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '县', 1, 1) + 1,
   INSTR (t.DESTINATION, '镇', 1, 1) - INSTR (t.DESTINATION, '县', 1, 1))  where  t.DESTINATION  like '%县%'
   and  t.DESTINATION  not  like  '%街道%'   
--更新县下面的街道   
update LRZ_XX   t set t.DESTINATIONSTREET= SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '县', 1, 1) + 1,
   INSTR (t.DESTINATION, '街道', 1, 1) - INSTR (t.DESTINATION, '县', 1, 1))

--更新区下面的镇
update LRZ_XX   t set t.DESTINATIONSTREET= SUBSTR (DESTINATION, 
   INSTR (t.DESTINATION, '区', 1, 1) + 1,
   INSTR (t.DESTINATION, '镇', 1, 1) - INSTR (t.DESTINATION, '区', 1, 1))
   where  t.DESTINATION  like '%镇%'
   and  t.DESTINATION  not  like  '%街道%'

复杂需要复制一张表的方法，不推荐，写起来麻烦，如果数据量大，复制表就会花很长时间

查询省;

查询省;
被拆分数据数据HOMEADDERSSPRIV  数据格式：山东省-日照市-岚山区|||碧桂园小区
LRZ_SSXX125copy表是从LRZ_SSXX125表复制出来的  

UPDATE   JKGL_CRB.LRZ_SSXX125  t1 set HOMEADDERSSPRIV = 
(select  t2.HOMEADDERSSPRIV  from
(SELECT  id,
  SUBSTR (HOMEADDERSS, 1, INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 1) - 1) AS HOMEADDERSSPRIV
FROM
  JKGL_CRB.LRZ_SSXX125copy where  HOMEADDERSS IS not NULL OR
DBMS_LOB.GETLENGTH(HOMEADDERSS) != 0) t2
where   t1.id = t2.id
  )

查询市：

查询市：
被拆分数据数据HOMEADDERSSPRIV  数据格式：山东省-日照市-岚山区|||碧桂园小区
UPDATE   JKGL_CRB.LRZ_SSXX125  t1 set HOMEADDERSSCITY = 
(select  t2.HOMEADDERSSCITY  from
(SELECT  id,
  SUBSTR (HOMEADDERSS, 
  INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 1) + 1,
   INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 2) - INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 1)-1)
    AS HOMEADDERSSCITY
FROM
  JKGL_CRB.LRZ_SSXX125copy where  HOMEADDERSS IS not NULL OR
DBMS_LOB.GETLENGTH(HOMEADDERSS) != 0) t2
where   t1.id = t2.id
  )

查询区

查询区：
被拆分数据数据HOMEADDERSSPRIV  数据格式：山东省-日照市-岚山区|||碧桂园小区
UPDATE   JKGL_CRB.LRZ_SSXX125  t1 set HOMEADDERSSAREA = 
(select  t2.HOMEADDERSSAREA  from
(SELECT  id,
  SUBSTR (HOMEADDERSS, 
  INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 2) + 1,
   INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '|||', 1, 1) - INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 2)-1)
    AS HOMEADDERSSAREA
FROM
  JKGL_CRB.LRZ_SSXX125copy where  HOMEADDERSS IS not NULL OR
DBMS_LOB.GETLENGTH(HOMEADDERSS) != 0) t2
where   t1.id = t2.id
  )

查询详细信息：

查询详细信息：
被拆分数据数据HOMEADDERSSPRIV  数据格式：山东省-日照市-岚山区|||碧桂园小区
UPDATE   JKGL_CRB.LRZ_SSXX125  t1 set HOMEADDERSSDETIAL = 
(select  t2.HOMEADDERSSDETIAL  from
(SELECT  id,
  SUBSTR (HOMEADDERSS, 
 /* INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 2) + 1,*/
   /*INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '|||', 1, 1) - INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '-', 1, 2)-1)*/
   INSTR (JKGL_CRB.LRZ_SSXX125copy.HOMEADDERSS, '|||', 1, 1) + 3)
    AS HOMEADDERSSDETIAL
FROM
  JKGL_CRB.LRZ_SSXX125copy where  HOMEADDERSS IS not NULL OR
DBMS_LOB.GETLENGTH(HOMEADDERSS) != 0) t2
where   t1.id = t2.id
  )