Insert select 和 update select 操作

最新推荐文章于 2023-06-14 11:59:47 发布

sophylyzz

最新推荐文章于 2023-06-14 11:59:47 发布

阅读量128

点赞数

分类专栏：数据库 Insert...select update ...insert

本文链接：https://blog.csdn.net/sophylyzz/article/details/84752247

版权

数据库 Insert...select update ...insert 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

--将已导入ecc_base_order_his历史表中的ECC_CYCLE_TRANSFER_ACCOUNTS数据有效期未过期预约类数据copy到
--ecc_base_order中
insert into ecc_base_order ebo 
select * from (select his.* from ecc_base_order_his his 
where his.order_no in(select b.order_no from ecc_cycle_transfer_accounts b  left join ecc_base_order a
on a.order_no=b.order_no 
where b.tran_type in('01','02') and  b.plan_status in('00','01') and b.cycle_type <> '00' and a.order_no is null
))
 --将ecc_base_order 和 ecc_cycle_transfer_accounts 中的VALID_STOP_DATE和VALID_TIME一致
update   ecc_base_order   ebo  set   ebo.valid_time = ( select   ecta.valid_stop_date  from 
ecc_cycle_transfer_accounts ecta 
where ecta.order_no = ebo.order_no and ecta.tran_type in('01','02') and  ecta.plan_status in('00','01') and ecta.cycle_type <> '00')
where  ebo.order_no = ( select  ecta.order_no  from  ecc_cycle_transfer_accounts ecta   where  ecta.order_no = ebo.order_no and ecta.tran_type in('01','02') and  ecta.plan_status in('00','01') and ecta.cycle_type <> '00')