Re: Update FROM clause? - Mailing list pgsql-general

From Tom Lane
Subject Re: Update FROM clause?
Date
Msg-id 1197.1036181939@sss.pgh.pa.us
Whole thread Raw
In response to Update FROM clause?  ("Booth, Robert" <Robert_Booth@intuit.com>)
List pgsql-general
"Booth, Robert" <Robert_Booth@intuit.com> writes:
> SELECT count(*)
>   FROM (allforms LEFT OUTER JOIN aanda ON allforms.file__no =
> aanda.file__no)
>  INNER JOIN formsin ON allforms.file__no = formsin.file__no
>  WHERE ... Omitted ...

> The Postgres UPDATE Query:
> UPDATE allforms
>    SET status = 'Okay to Edit'
>   FROM formsin LEFT OUTER JOIN aanda ON formsin.file__no = aanda.file__no
>  WHERE allforms.file__no = formsin.file__no
>    AND ... Omitted, matches the Access Query ...

Those are not the same thing at all.  Your UPDATE is more like

SELECT ...
FROM allforms,
     (formsin LEFT OUTER JOIN aanda ON formsin.file__no = aanda.file__no)
WHERE allforms.file__no = formsin.file__no
  AND ... Omitted, matches the Access Query ...

which is reversing the order of the inner and outer join steps.
Depending on your data this might yield quite a different result.

            regards, tom lane

pgsql-general by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: [Q] Sequences, last_value and inserts
Next
From: "Roderick A. Anderson"
Date:
Subject: Re: [Q] Sequences, last_value and inserts