Re: Is SQL VALUES() possible in UPDATE?

I find that it is possible, with a good night's sleep and some LATERAL thinking.

Simplified file:

create table QTEMP/FLATFL(FLDATA)
as ( values '123456PAYMORE London ',
'321456Cash Sales ',
'876123Baltimore Branch ' )
with data

Simplified change requirement:

update QTEMP/FLATFL
set FLDATA = coalesce(
(
select overlay(FLDATA placing
'PAYMORE ' || substr(replace(replace( branchname,
'Branch',''),'Sales',''),1, 10)
from 7 for 16)
from lateral(values( substr(FLDATA, 7,18))) lt(branchname)
where upper( lt.branchname ) not like '%PAYMORE%'
and regexp_like( branchname,'(Sales|Branch)')
and branchname is not null
) , FLDATA )

Result:

FLDATA
123456PAYMORE London
321456PAYMORE Cash
876123PAYMORE Baltimore