oracle 针对多条匹配数据按照指定顺序进行选择一条更新

最新推荐文章于 2023-06-13 16:59:59 发布

血空不吊坎

最新推荐文章于 2023-06-13 16:59:59 发布

阅读量1.2k

点赞数 1

文章标签： oracle java

本文链接：https://blog.csdn.net/qq_33967604/article/details/112009039

版权

oracle更新数据，对于按照某个特定排序，选择其中一条进行更新

解决办法

解决办法

select tb.*,
               tca.account_id,
               tca.customer_id tca_customer_id,
               row_number() over(PARTITION BY tb.entity_id, tb.customer_code, tb.sales_main_type_code, tb.operator_code order by decode(uu.unit_type, '常规', '1', '电商', '2', '网批', '3', '家装', '4', '旗舰店', '5', '多品', '6')) rn
          from cims.T_CUSTOMER_COOPERATE_MODEL tb,cims.t_customer_account  tca, cims.up_org_unit uu
         where tb.entity_id = uu.entity_id
         and tb.entity_id = tca.entity_id
           and tb.sales_center_code = uu.code
           and tb.operator_code = tca.customer_code
           and uu.active_flag = 'T'
           and uu.is_enabled = 'T'
           and tb.customer_status = 'Active'
           and tb.org_status = 'Active'
           and tca.account_status in ('1', '-1')
   and tca.active_flag = 'Y'

1.选择PARTITION BY 进行分组row_number() over进行排序。

2.对选择的数据进行按照特定排序进行选择优先的一条


   select tem.customer_code,
       tem.sales_center_id,
       tem.sales_center_code,
       tem.sales_center_name,
       tem.sales_main_type_code,
       tem.entity_id,
       tem.operator_code,
       tem.operator_name,
       tem.account_id,
       tem.tca_customer_id
  from (select tb.*,
               tca.account_id,
               tca.customer_id tca_customer_id,
               row_number() over(PARTITION BY tb.entity_id, tb.customer_code, tb.sales_main_type_code, tb.operator_code order by decode(uu.unit_type, '常规', '1', '电商', '2', '网批', '3', '家装', '4', '旗舰店', '5', '多品', '6')) rn
          from cims.T_CUSTOMER_COOPERATE_MODEL tb,cims.t_customer_account  tca, cims.up_org_unit uu
         where tb.entity_id = uu.entity_id
         and tb.entity_id = tca.entity_id
           and tb.sales_center_code = uu.code
           and tb.operator_code = tca.customer_code
           and uu.active_flag = 'T'
           and uu.is_enabled = 'T'
           and tb.customer_status = 'Active'
           and tb.org_status = 'Active'
           and tca.account_status in ('1', '-1')
   and tca.active_flag = 'Y') tem
 where rn = 1

再对于想更新的表进行merge into的更新

merge into cims.T_POL_CUSTOMER_LINE tl
  using (select tem.customer_code,
       tem.sales_center_id,
       tem.sales_center_code,
       tem.sales_center_name,
       tem.sales_main_type_code,
       tem.entity_id,
       tem.operator_code,
       tem.operator_name,
       tem.account_id,
       tem.tca_customer_id
  from (select tb.*,
               tca.account_id,
               tca.customer_id tca_customer_id,
               row_number() over(PARTITION BY tb.entity_id, tb.customer_code, tb.sales_main_type_code, tb.operator_code order by decode(uu.unit_type, '常规', '1', '电商', '2', '网批', '3', '家装', '4', '旗舰店', '5', '多品', '6')) rn
          from cims.T_CUSTOMER_COOPERATE_MODEL tb,cims.t_customer_account  tca, cims.up_org_unit uu
         where tb.entity_id = uu.entity_id
         and tb.entity_id = tca.entity_id
           and tb.sales_center_code = uu.code
           and tb.operator_code = tca.customer_code
           and uu.active_flag = 'T'
           and uu.is_enabled = 'T'
           and tb.customer_status = 'Active'
           and tb.org_status = 'Active'
           and tca.account_status in ('1', '-1')
   and tca.active_flag = 'Y') tem
 where rn = 1) TC
  on (TC.Entity_Id = tl.entity_id and tc.sales_main_type_code = tl.sales_main_type_code and tc.customer_code = tl.source_customer_code /*and tc.sales_center_code = tl.source_sales_center_code and rownum = 1*/  )
  when matched then
    update
       set tl.target_customer_id       = tc.tca_customer_id,
           tl.target_customer_code     = tc.operator_code,
           tl.target_customer_name     = tc.operator_name,
           tl.target_sales_center_id   = tc.sales_center_id,
           tl.target_sales_center_code = tc.sales_center_code,
           tl.target_sales_center_name = tc.sales_center_name,
           tl.last_updated_date        = sysdate
     where tl.customer_head_id = ?