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 = 0from (select min(datetime) as datetime, membid from      sessions2 group by
membid)as mindatewhere 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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco