Re: Update sql question

Tom Lane wrote:

>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
>
>
>
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