Re: Wacky query plan, why?

Yes I see, no words about FROM cause in SQL92/99, but
it seems like Postgres supports that.

So bottom line:
insted of

update prod.t_results set fan = a.fullname, fin=i.fullname
from prod.t_results r inner join prod.t_agn a
    on r.faid = a.aid
inner join prod.t_inv i
    on r.fiid = i.iid
        where r.docid = 22544257;

I should use

update prod.t_results set fan = a.fullname, fin=i.fullname
from prod.t_results r inner join prod.t_agn a
    on r.faid = a.aid
inner join prod.t_inv i
    on r.fiid = i.iid
        where r.docid = 22544257 and prod.t_results.docid =
r.docid;

BTW, what it's doing in a first place, looks up tuples generated in FROM
clause
against prod.t_results table?

-----Original Message-----
From: Mike Mascari [mailto:mascarm@mascari.com]
Sent: Friday, July 25, 2003 5:25 PM
To: Maksim Likharev
Cc: Stephan Szabo; pgsql-general
Subject: Re: [GENERAL] Wacky query plan, why?


Maksim Likharev wrote:

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

13.10  <update statement: searched>

         Function

         Update rows of a table.

         Format

         <update statement: searched> ::=
              UPDATE <table name>
                SET <set clause list>
                [ WHERE <search condition> ]


So, for SQL92:

UPDATE prod.t_results
SET expdate = (
 SELECT e.termdate
 FROM work.termdate e
 WHERE e.docid = prod.t_results.docid
);

If a 'termdate.docid' does not necessarily exist for every
't_results.docid' then you must further qualify the update to ensure
expdate won't be set to NULL (or die trying):

UPDATE prod.t_results
SET expdate = (
 SELECT e.termdate
 FROM work.termdate e
 WHERE e.docid = prod.t_results.docid
)
WHERE EXISTS (
 SELECT 1
 FROM work.termdate e
 WHERE e.docid = prod.t_results.docid
);

That's SQL92 and it's ugly. I prefer the PostgreSQL extended form:

UPDATE prod.t_results
SET expdate = work.termdate.termdate
WHERE prod.t_results.docid = work.termdate.docid;

Hope that helps,

Mike Mascari
mascarm@mascari.com