Re: Is SQL VALUES() possible in UPDATE?

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

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