Update FROM clause?
От | Booth, Robert |
---|---|
Тема | Update FROM clause? |
Дата | |
Msg-id | 419D2EB7B461D411A53B00508B69181D06232953@sdex02.sd.intuit.com обсуждение исходный текст |
Ответы |
Re: Update FROM clause?
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: