Re: Update sql question

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