From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Don Isgitt <djisgitt(at)soundenergy(dot)com> |
Cc: | postgresql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update sql question |
Date: | 2005-06-07 15:30:39 |
Message-ID: | 8747.1118158239@sss.pgh.pa.us |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Phil Endecott | 2005-06-07 15:35:31 | Re: CPU-intensive autovacuuming |
Previous Message | Marty Scholes | 2005-06-07 15:30:13 | Re: Postgresql and Software RAID/LVM |