with tmp as 与 update 不能用

本文尝试探讨了在 Oracle 数据库中使用 WITH AS 与 UPDATE 语句结合的可能性及遇到的问题。作者通过多种方式试图实现 UPDATE 语句与 WITH 子句的结合,并记录了其中的错误和意外的成功案例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

with tmp as () 

后面直接接update的时候会报错,没发现select


本来想着

with tmp as () 

update

set col1 = ( select tmp.col from tmp ),

set col2 = ( select tmp.col2 from tmp ),

...

where col3 in ( select tmp.col3 from tmp )

....

执行报错了,没有select,这个应该大家都发现了

然后呢

我换了个法子


update

set col1 = (  with tmp as () 

select tmp.col from tmp ),

set col2 = ( select tmp.col2 from tmp )

...

where col3 in ( select tmp.col3 from tmp )

....

我告诉你,这个语句看起来跟奇怪

但是,它是可以执行的,而且不报错

是不是很神奇啊,

虽然这个语句看起来很奇怪,但是,它真的是可以执行的

神奇吧

那么,你是不是觉得峰回路转,with as与update可以用了呢?


其实不然啊

我试了执行了一下,结果为0

但是我不用with as 居然执行结果是18

这里你们明白了嘛

还是不可以执行

当然,也可能是我的技术不好,或者我是不是没考虑update语句的执行顺序


我是不是应该把with as 放到where里面去呢

update

set col1 = (  select tmp.col from tmp ),

set col2 = ( select tmp.col2 from tmp )

...

where col3 in (  with tmp as () 

                             select tmp.col3 from tmp )

....

这样不知道行不行

本来是想试试的

结果发现,刚才执行没有with as的时候被commit了

然后,测试数据没有了,造起来很麻烦

所以,有兴趣的自己找个场景试试吧,我觉得应该也是不行的


最近加班有点累,前天加班到三点,昨晚还被拖到八点多,有点晕,脑袋转不动了。就这样

最最关键的还是oracle 不支持update from啊

不然

哪要这么纠结呢



with tmp_table as (select update_time, e1 as integratedindex, e2 as pm25, e3 as pm10, e4 as co, e5 as no2, e6 as so2, e7 as o3, e8 as aqi, e9 as tsp from dc_dm_air_city_air_data_national_ranking_d t lateral view explode_json_array_json(integrated_index_list) tmp1 as e1 lateral view explode_json_array_json(pm25_list) tmp2 as e2 lateral view explode_json_array_json(pm10_list) tmp3 as e3 lateral view explode_json_array_json(co_list) tmp4 as e4 lateral view explode_json_array_json(no2_list) tmp5 as e5 lateral view explode_json_array_json(so2_list) tmp6 as e6 lateral view explode_json_array_json(o3_list) tmp7 as e7 lateral view explode_json_array_json(aqi_list) tmp8 as e8 lateral view explode_json_array_json(tsp_list) tmp9 as e9 where air_region_key = #{ranking} and update_time BETWEEN date_format('${sTime}', '%Y-%m-%d') AND date_format('${eTime}', '%Y-%m-%d')), yoy_tmp_table as (select update_time, e1 as integratedindex, e2 as pm25, e3 as pm10, e4 as co, e5 as no2, e6 as so2, e7 as o3, e8 as aqi, e9 as tsp from dc_dm_air_city_air_data_national_ranking_d t lateral view explode_json_array_json(integrated_index_list) tmp1 as e1 lateral view explode_json_array_json(pm25_list) tmp2 as e2 lateral view explode_json_array_json(pm10_list) tmp3 as e3 lateral view explode_json_array_json(co_list) tmp4 as e4 lateral view explode_json_array_json(no2_list) tmp5 as e5 lateral view explode_json_array_json(so2_list) tmp6 as e6 lateral view explode_json_array_json(o3_list) tmp7 as e7 lateral view explode_json_array_json(aqi_list) tmp8 as e8 lateral view explode_json_array_json(tsp_list) tmp9 as e9 where air_region_key = #{ranking} and update_time between date_sub(date_format('${sTime}', '%Y-%m-%d'), interval 1 YEAR) and date_sub(date_format('${eTime}','%Y-%m-%d'), interval 1 YEAR) )优化一下该sql查询,现在太慢了
03-11
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值