My be I too spoiled by MS SQL Server, but does'nt
syntax:
update prod.t_results set expdate=e.termdate from
work.termdate e, prod.t_results r where e.docid=r.docid;
or
update prod.t_results set expdate=e.termdate from
work.termdate e inner join prod.t_results r on e.docid=r.docid;
is standard SQL-92 update FROM form?
just trying to understand.
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Friday, July 25, 2003 4:12 PM
To: Maksim Likharev
Cc: pgsql-general
Subject: Re: [GENERAL] Wacky query plan, why?
On Fri, 25 Jul 2003, Maksim Likharev wrote:
> I have 2 queries, one is never returns, like explain shows
677195846.00
> cost
> and another doing the same job works ( cost 6072.00 )
>
> I do not understand one thing, why query number one, generates so
> unbelievably
> screwed up plan?
>
> why it does not use index?
>
> query # 1:
>
------------------------------------------------------------------------
> ------
> explain update prod.t_results set expdate=e.termdate from
> work.termdate e, prod.t_results r where e.docid=r.docid;
This is almost certainly not what you meant. You haven't constrained
the update table (t_results) to the results of the from. Adding
t_results
r to the from joins an additional copy of the table (it's not an alias
for
the update table).
Why not just
update prod.tresults set expdate=e.termdate from work.termdate where
e.docid=t_results.docid;
?