update(
select id, word, status
case when sm = 1 and status = 'Renew'
then coalesce(lgst, 'Add')
else status
end as status1,
timestamp,
clob_column
from
(select id, word, status,
sum(case when status = 'Renew' then 1 else 0 end) over (partition by id order by timestamp) as sm,
lag(status) over (partition by id order by timestamp) as lgst, timestamp, clob_column
from your_table)_
set clob_column = REPLACE( clob_column , '"key":'||status, '"key":'||status1);
在查询上方运行给我错误
set clob_column = REPLACE（clob_column，'“ key”：'||状态，'“ key”：'|| status1）错误在命令行：26列：7错误报告：SQL错误：ORA-01733：不允许虚拟列此处01733。00000-“此处不允许使用虚拟列”
我的Oracle版本12c
更新子查询有一些怪异的限制，可以通过使用MERGE语句来避免：
merge into your_table
using
(
select
id, word, status, timestamp, clob_column,
case when sm = 1 and status = 'Renew' then coalesce(lgst, 'Add') else status end as status1
from
(
select
id, word, status, timestamp, clob_column, rowid the_rowid,
sum(case when status = 'Renew' then 1 else 0 end) over (partition by id order by timestamp) as sm,
lag(status) over (partition by id order by timestamp) as lgst
from your_table
)
) new_rows
on (your_table.rowid = new_rows.rowid)
when matched then update set
clob_column = replace(clob_column , '"key":'||status, '"key":'||status1);
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句