Re: update field based on substring of numeric field

On 08-Oct-2015 09:37 -0600, rob wrote:

I'm wondering if you have no matching record in SHactfile therefore
you have the null? You might have to change

update tstmis/actfile a
set umnum =
(select substring(digits(umnum), 6, 4)
from tstmis/shactfile b
where a.umact=b.umact and b.umnum>0 and a.umnum=0 )

to something like

update tstmis/actfile a
set umnum =
(select substring(digits(ifnull(B.umnum,'0000000000')), 6, 4)
from tstmis/shactfile b
where a.umact=b.umact and b.umnum>0 and a.umnum=0 )

That [as-inferred is the conceptual intent, though surely not composed as intended] would assist only if the values of UMNUM in SHactfile were NULL; i.e. that revision would not assist for an issue of unmatched records.

To replace the scalar NULL result for any unmatched rows, the IFNULL [or VALUES or COALESCE or CASE] scalar should be coded around the scalar subselect. But that is only an option when the update of all rows is desirable\intended [in the example from the OP], because without the effect of what an EXISTS predicate provides, all rows will be updated.

For example, to set A.UMNUM to zero when no matching record is found, irrespective the current value of A.UMNUM [not to imply that would be a desirable effect]:

update tstmis/actfile a
set umnum =
ifnull( ( select substring(digits(umnum), 6, 4)
from tstmis/shactfile b
where a.umact=b.umact and b.umnum>0 and a.umnum=0 )
, 0 )

While Buck proposed possibly the additional predicate to limit what rows were updated, limited to only those rows where already the value of A.UMNUM=0 [i.e. the same predicate used to find the matching row], that may or may not exhaustively eliminate all unmatched records, so the typical repeated subquery in an EXISTS predicate [as Satya suggested] is generally coded as preventive... and because updating every row irrespective of a matching row being found, is generally undesirable.