Tom Lane wrote:
Don Isgitt <djisgitt@xxxxxxxxxxxxxxx> 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
Ah, quite so. Thank you, Tom and Richard for your spot on help. Tom, I
appreciate your concern for my limit 1; I confess it is a lazy way out.
There are many multiple duplicate entries (oldopr and newopr), so rather
than cleaning up the table, ...
Don
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
