From: | Don Isgitt <djisgitt(at)soundenergy(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | postgresql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update sql question |
Date: | 2005-06-07 16:34:25 |
Message-ID: | 42A5CC91.1010201@soundenergy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
>Don Isgitt <djisgitt(at)soundenergy(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-06-07 16:42:03 | Re: CPU-intensive autovacuuming |
Previous Message | Peter Eisentraut | 2005-06-07 16:15:21 | Re: Debian Stable goes from Woody to Sarge!! |