Re: Update FROM clause?

> "Booth, Robert" <Robert_Booth(at)intuit(dot)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