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);
Running above query giving me error
set clob_column = REPLACE( clob_column , '"key":'||status, '"key":'||status1) Error at Command Line:26 Column:7 Error report: SQL Error: ORA-01733: virtual column not allowed here 01733. 00000 - "virtual column not allowed here"
My oracle version 12c