Turn this SELECT into an UPDATE

[Viking]

I need to update two fields in a file, if they're zero, with the values in two equivalent fields from a different file, for records that have certain matching values.

Here is my SELECT statement that gives me the records I want:

select JOBS.rpcostm, JOBS.rpcostl
      from drtstdat.frpjobs JOBS
              inner join drtstdat.F58450 on JOBS.rpwarr = cw$new and JOBS.rpstore = cw$str and JOBS.rpreg = cw$rg# and JOBS.rppos = cw$pos and cw$ntt = 'R'
           inner join drtstdat.frpjoba JOBA on JOBS.rpjob = JOBA.rpjob and JOBA.rpstat = 40
         where rptype = 'W'
           and year(JOBA.rpts) >= year(Current_Date) - 1
           and cw@pa = 0 and cw@rc = 0​

I want to update file F58450 fields CW@PA and CW@RC, if they're zero, with the values from file FRPJOBS fields RPCOSTM and RPCOSTL, where the records match as defined by the inner join of FRPJOBS and F58450.

So now I need to change this SELECT statement into an UPDATE of file F58450, fields cw@pa and cw@rc.

So do I want to do convert the above statement to a CTE and use that, or should I do something like this? Or what's the best way to do this?

update drtstdat.F58450
set (cw@pa, cw@rc) =
(select JOBS.rpcostm, JOBS.rpcostl
 from​

Thanks in advance for your help!

[paolinosal]

Hi Viking,

I suggest to you the use of MERGE. It seems a bit difficult to understand, but really I think this is the right choice.

The syntax is something like this:

merge into F58450 using ( the select statement) as table1 on (the field to join)
when matched and CW@PA and CW@RC = 0
then update set CW@PA = RPCOSTM, CW@RC = …

try it on a working table.

Let me know