Re: Is SQL VALUES() possible in UPDATE?

Is the QTEMP table maybe a little overkill. Maybe think in the direction of
merge into. I have not the entire logick for you - but try to "read my
mind" here :

create table qgpl.x2 ( name varchar(256) , number int);

insert into qgpl.x2 values ('x' ,55);

merge into qgpl.x2 as a
using (values
('x' , 33)
) as b (name , number)
on a.name = b.name
when matched then
update set a.number = b.number




On Thu, Nov 5, 2020 at 8:26 AM Arnie Flangehead <arnie.flangehead@xxxxxxxxx>
wrote:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com