I have a question about the UPDATE FROM clause. This is non standard SQL,
but looks like it might work for me in this situation.
The Situation:
I have to convert an ugly Access query to run on Postgres. Now normally
this wouldn't be a problem except that this particular Access query uses
joins to validate that it has the right record to update.
The Access UPDATE Query:
UPDATE (allforms LEFT JOIN aanda ON allforms.file__no = aanda.file__no)
INNER JOIN formsin ON allforms.file__no = formsin.file__no
SET allforms.status = 'Okay to Edit'
WHERE ... Omitted doesn't really matter ...
Now as you can see in the above query there is a LEFT join being joined to
another table with an INNER join. For Access this works just fine, and if I
recreate this setup in a Postgres select it works just fine.
Postgres SELECT of Access Query:
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 ...
Count = 955
Ok everything looks good here, but when I try to convert this query to
Postgres I need to rewrite it to use the FROM clause, and when I do that I
can't use the same FROM syntax from the above queries, I need to change it.
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 ...
Now at this point everything looks good and looks like it should work so I
test running it as a select to make sure I get the same number.
Postgres SELECT of Postgres Query:
SELECT count(*)
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 ...
Count = 955
This looks good, but I do get a notice from Postgres - NOTICE: Adding
missing FROM-clause entry for table "allforms". But still this is all good.
Until I decided to run the Postgres UPDATE query above and I get only 813
rows updated. Is Postgres making a different assumption in my SELECT query
than in my UPDATE query? Is there something that I'm missing in trying to
use the UPDATE FROM clause? Any and all help is greatly appreciated this is
the 1st of 6 queries like this that I have to convert.
Thanks,
Rob
