批量修改表中某些字段的值

最新推荐文章于 2023-07-05 08:55:09 发布

iteye_6593

最新推荐文章于 2023-07-05 08:55:09 发布

阅读量501

点赞数

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.csdn.net/iteye_6593/article/details/82599967

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一使用场景
表中某一字段值原值"123-ABC”，但要求修改为"123ABC“，数据库中很多表有关联此字段值

二修改方式


1 首先清除主键约束
select 'ALTER  TABLE ' || consc.table_name || ' disable  constraint ' ||
       consc.constraint_name
  from dba_cons_columns consc,
       (select tb.owner, tb.table_name, cols.column_name
          from dba_tables tb, dba_tab_cols cols
         where tb.OWNER = 'ORCL'
           and cols.OWNER = tb.OWNER
           and tb.table_name = cols.table_name
           and cols.column_name in ('XLID', 'TQID')) tbs
 where consc.table_name = tbs.table_name
   and consc.owner = 'ORCL'
   and consc.column_name in ('XLID', 'TQID')
 order by consc.constraint_name


2 其次生成更新语句
select tb.owner,
       ' update ' || tb.table_name || ' set ' || cols.column_name ||
       '= substr(' || cols.column_name || ',0,instr(' || cols.column_name ||
       ',''-'')-1)||substr(' || cols.column_name || ',instr(' ||
       cols.column_name || ',''-'')+1,length(' || cols.column_name ||
       ')) where instr(' || cols.column_name || ',''-'')>0',
       cols.column_name
  from dba_tables tb, dba_tab_cols cols
 where tb.OWNER = 'ORCL'
   and cols.OWNER = tb.OWNER
   and tb.table_name = cols.table_name
   and cols.column_name in ('XLID', 'TQID')


3 恢复主键约束
select 'ALTER  TABLE ' || consc.table_name || ' able  constraint ' ||
       consc.constraint_name
  from dba_cons_columns consc,
       (select tb.owner, tb.table_name, cols.column_name
          from dba_tables tb, dba_tab_cols cols
         where tb.OWNER = 'ORCL'
           and cols.OWNER = tb.OWNER
           and tb.table_name = cols.table_name
           and cols.column_name in ('XLID', 'TQID')) tbs
 where consc.table_name = tbs.table_name
   and consc.owner = 'ORCL'
   and consc.column_name in ('XLID', 'TQID')
 order by consc.constraint_name