度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle 用一个表字段更新另一个表字段三种方法 [打印本页]

作者: 张兴康 时间: 2020-4-15 15:03
标题: Oracle 用一个表字段更新另一个表字段三种方法
1. update （select .....） set column1 = column2;

update (select iy.company_name company_name1, cc.company_name_jc company_name2
from income_year_item iy , city_company cc

where iy.company_code = cc.code
)
set company_name1 = company_name2;

2.只能单行子查询s

update  CITY_PROJECT_SCALE_INFO c  set
(c.value) = (
      select d.value from CITY_PROJECT_SCALE_INFO @test d where d.project_id = '7d7fd580a06240b2a9137dc2bbe831e9'
         and d.project_id = c.project_id and c.company_code = d.company_code
)
  where exists (
select 1 from  CITY_PROJECT_SCALE_INFO @test d where d.project_id = '7d7fd580a06240b2a9137dc2bbe831e9'
         and d.project_id = c.project_id and c.company_code = d.company_code
)

3.使用merg inot 语句

--更新生产基础字段
merge into city_cfg_data_column_common cf1 using
   city_cfg_data_column_common2 cf2 on
   (cf1.resourceid = cf2.resourceid)
when matched then
      update set cf1.template_type = cf2.template_type,
                  cf1.chinese_name = cf2.chinese_name,
                  cf1.column_name = cf2.column_name,
                  cf1.column_type = cf2.column_type,
                  cf1.column_size = cf2.column_size
when not matched then
      insert (cf1.resourceid,cf1.template_type,cf1.chinese_name,cf1.column_name,cf1.column_type,cf1.column_size
            ,cf1.is_can_edit,cf1.is_unique,cf1.is_can_cover,cf1.show_order)
      values (cf2.resourceid,cf2.template_type,cf2.chinese_name,cf2.column_name,cf2.column_type,cf2.column_size
            ,cf2.is_can_edit,cf2.is_unique,cf2.is_can_cover,cf2.show_order)

作者: 张兴康 时间: 2020-4-15 15:03

作者: caixuqad 时间: 2020-4-15 18:10

作者: Adam丶鱼 时间: 2020-4-15 19:35

caixuqad 发表于 2017-5-15 18:10

{:3_50:

作者: 张兴康 时间: 2020-4-16 16:04

caixuqad 发表于 2017-5-15 18:10

欢迎光临度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/)