Обсуждение: Update sql question

Hello,

First, software info:

gds2=# select version();

version

--------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)

Now, description of what I did and the results:


gds2=# begin;
BEGIN
gds2=# select count(*) from master where state='NM' and operator is null;
count
-------
     0
(1 row)

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;
UPDATE 88486
gds2=# select count(*) from master where state='NM' and operator is null;
count
-------
   261
(1 row)

gds2=# rollback;
ROLLBACK

Now, what I was trying to do:

master table has ~1000000 records, opr_match~120000 records and each has
a state field. master has an operator field (among many others),
opr_match has an oldopr and newopr field. I want to update master
operator field iff opr_match.oldopr=master.operator; if no match, just
update to self. Fine.

What am I doing wrong that gives me the 261 null operator fields after
the update?

Thank you very much for your assistance.

Don

p.s. The "operator is not null" where clause is needed for other states
that do have null operator values.

Don Isgitt wrote:
>
> 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;

> What am I doing wrong that gives me the 261 null operator fields after
> the update?

Hmm - haven't tested this, but what would happen if the subselect
returned no matches? Quickest test is probably to add a dummy value to
the coalesce and see if that takes the place of the 261 nulls. If it
doesn't, the subselect isn't matching for those rows.
--
   Richard Huxton
   Archonet Ltd

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

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

Don Isgitt <djisgitt@soundenergy.com> writes:
> Tom Lane wrote:
>> 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?

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

Perhaps write the sub-select as

(select distinct newopr from opr_match
 where state=master.state and oldopr=master.operator)

so that you'll get an error if there's more than one value for newopr in
the table.  I'm not sure what performance hit you'll take, but checking
the data for self-consistency is a good idea in my book, especially when
you already know it's not very clean ...

            regards, tom lane