Update FROM clause?

From: "Booth, Robert" <Robert_Booth(at)intuit(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Update FROM clause?
Date: 2002-11-01 19:24:14
Message-ID: 419D2EB7B461D411A53B00508B69181D06232953@sdex02.sd.intuit.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Bakker 2002-11-01 19:28:27 Re: Questions on specifying table relationships
Previous Message Wei Weng 2002-11-01 19:18:37 Re: FIFO Queue Problems