Don Isgitt <djisgitt@soundenergy.com> writes:
> gds2=# update master set operator=(select
> coalesce(newopr,master.operator) from opr_match where state=master.state
> and oldopr=master.operator limit 1) where state='NM' and operator is not
> null;
I think what you want is
gds2=# update master set operator=coalesce((select
newopr from opr_match where state=master.state
and oldopr=master.operator limit 1), master.operator)
where state='NM' and operator is not null;
The trouble with putting the coalesce inside the sub-select is that it
doesn't save you in the case where there is no match on oldopr and so
the sub-select returns zero rows. That's interpreted as a NULL result
at the outer level.
BTW, I find the "limit 1" a bit scary --- if there are multiple matches,
this coding will select a random one of them. Is that really what you
want?
regards, tom lane