Re: select is fast, update based on same where clause is slow

Jeff,

I think that you're running into a syntactical problem here:

> Then I issue an update as follows (to update those 74,000+ rows):
> update sessions2 set sinceinception = 0
> from sessions2 s, (select min(datetime) as datetime, membid from
> sessions2
> group by membid) as mindate
> where s.membid = mindate.membid
> and s.datetime = mindate.datetime;

In the query above, there is no linkage between the instance of
sessions2 (sessions2) you are updating and the instance you are limiting
(s). As a result, you are running an update on 74,000^2 rows (about 55
trillion), and if the query ever completed you would find that
sinceinception would be set to 0 for all rows in sessions2. Your query
should read:

update sessions2 set sinceinception = 0
from (select min(datetime) as datetime, membid from
sessions2 group by membid) as mindate
where sessions2.membid = mindate.membid
and sessions2.datetime = mindate.datetime;

It's a peculiarity of Update queries that the name of the updated table
cannot be aliased anywhere in the query. To add to the confusion, in MS
Transact SQL, you are expected to repeat the name of the updated table
in the FROM clause, while in Postgres such repetition is prohibited.

This is mainly due to the fact that UPDATE ... FROM is not well-defined
in the SQL 92 standard.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco