update select 语句和merge into语句
来源:互联网 发布:电影编辑软件 知乎 编辑:程序博客网 时间:2024/05/29 12:44
查看执行计划,发现merge语句比update预计效果高很多
update DW_MEMBER_D dset d.mem_grade=
(select e.grade_idfrom DW_MEMBER_exp ewhere e.card_no=d.card_no)
update DW_HOTELORDER_F b
set(contacter_mbl_pr, contacter_mbl_cty)=
(select a.province, a.city
from MOBILE_BELONGINGNESS_ORDER a
where a.reser_no= b.reser_no)
update WC_CONCALL_A f
set f.emp_wid = (select c.empwidfrom (select a.month_wid,
a.postn_wid,
b.emp_wid as empwid,
a.prod_ln_wid /*, b.postn_type_cd_i*/
from WC_CONCALL_A a, wc_postn_hist_f b
where a.month_wid = b.month_wid
and a.postn_wid = b.postn_wid) c
where c.month_wid = f.month_wid
and c.postn_wid = f.postn_wid
and c.prod_ln_wid = f.prod_ln_wid)
MERGE INTO WC_CONCALL_A f
using (select a.month_wid,a.postn_wid,
b.emp_wid as empwid,
a.prod_ln_wid /*, b.postn_type_cd_i*/
from WC_CONCALL_A a, wc_postn_hist_f b
where a.month_wid = b.month_wid
and a.postn_wid = b.postn_wid) c
ON (c.month_wid = f.month_wid and c.postn_wid = f.postn_wid and c.prod_ln_wid = f.prod_ln_wid)
WHEN MATCHED THEN
UPDATE SET f.emp_wid = c.empwid
MERGE INTO ZZ_HAP p
USING (select 0 as row_wid,
'NA' as code_id,
1 lvl,
1 par_id,
'OTHER' en_name,
'其他' cn_name,
'Y' isleaf_flg,
'Unspecified' long_name,
'OTHER' datasource_type,
'OTHER' integration_id,
'en' lang_id,
'N' src_delete_flg,
'Y' active_flg,
'999999' disp_order,
to_date('19500101', 'yyyymmdd') src_eff_from_dt,
to_date('99991231', 'yyyymmdd') src_eff_to_dt,
to_date('19500101', 'yyyymmdd') created_on_dt,
to_date('99991231', 'yyyymmdd') changed_on_dt,
'Y' current_flg,
to_date('19500101', 'yyyymmdd') begin_dt,
to_date('99991231', 'yyyymmdd') end_dt,
sysdate w_insert_dt,
sysdate w_update_dt
from dual) np
ON (p.row_wid = np.row_wid)
WHEN NOT MATCHED THEN
insert(
begin_dt ,
end_dt ,
w_insert_dt ,
w_update_dt
)
VALUES
(
np.begin_dt,
np.end_dt,
sysdate,
sysdate
)
WHEN MATCHED THEN
UPDATE
SET
p.w_update_dt = sysdate,
p.master_partner_type = np.code_id,
p.partner_type = np.code_id;
mergeinto DW_ORDER_F aa
using MOBILE_BELONGINGNESS_ORDER bb
on (aa.reser_no= bb.reser_noand aa.create_date_wid >=20160401and aa.create_date_wid <= 20160701)
whenmatchedthen
update
set aa.contacter_mbl_pr= bb.province, aa.contacter_mbl_cty= bb.city
- update select 语句和merge into语句
- 使用merge into优化update语句
- 使用Merge Into 语句实现 Insert/Update
- 使用Merge Into 语句实现 Insert/Update
- Oracle-----Update语句优化之merge into
- Merge Into 语句代替Insert/Update
- INSERT...SELECT 语句和SELECT...INTO 语句
- SQL语句:update---select,insert---select,select---into
- INSERT INTO SELECT语句 和 SELECT INTO FROM语句
- Merge Into 语句代替Insert/Update在Oracle中的应用
- merge into 语句用法
- Merge Into 语句
- merge into语句
- select into 和 insert into select 两种表复制语句
- select into 和 insert into select 两种表复制语句
- select into 和 insert into select 两种表复制语句
- select into 和 insert into select 两种表复制语句
- select into 和 insert into select 两种表复制语句
- Android-多种进度条的使用方法
- linux grep命令详解
- SERVLET 学习笔记
- VIM_Pulgin
- 动态规划之n个元素出栈顺序种数
- update select 语句和merge into语句
- Visual Studio中使用正则表达式快速统计项目总共代码行数
- [转载]数据驱动精准化营销在大众点评的实践
- Java中的CopyOnWrite容器
- shell判断文件是否存在
- DIALOG
- stm32 Flash操作
- Error configuring application listener of class org.springframework.web.context.ContextLoaderListene
- 编译AOSP方便阅读源码