Re: Update FROM clause?

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

I was thinking about that, but when I run the selects I end up with the same
records.  I've been checking them since I sent the email.  So I'm wondering
what I'm missing, or how I can get the UPDATE to perform the same as the
SELECT, that uses the same structure.

Rob