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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
