The following bug has been logged online:

Bug reference: 2303
Logged by: Massimo Fidanza
Email address: malix0 (AT) gmail (DOT) com
PostgreSQL version: 8.1
Operating system: Linux
Description: UPDATE from manual is incorrect
Details:

Hi all, I have an update similar to the one included in Postgresql
documentation in Postgresql 8.1 -> VI. Reference -> I. SQL Commands ->
UPDATE

The query is the last but one

UPDATE employees SET last_closed_deal = deal.id
FROM accounts JOIN deals ON (account.id = deal.account_id)
WHERE deal.employee_id = employees.id
AND deal.name = 'Rocket Powered Skates'
AND accounts.name = 'Acme Corporation'
ORDER BY deal.signed_date DESC LIMIT 1;

this query is not correct and doesn't work with postgresql 8.1.


My query that doesn't work is the first and I modify it in second form that
is the correct one.

------------- FIRST QUERY (NOT WORKING) ---------------
update
tblstock
set
npezzi = sum(sr.npezzi)
,npezzirimanenti = sum(sr.npezzi)
,modelloid = sr.modelloid
,objid = sr2.objid
,tipostockid = 3
from
tblstockrighe sr join tblstockrighe sr2
on sr.modelloid = sr2.modelloid and sr2.id = 110
where
sr.stockid = tblstock.id
and sr.stockid = 270
group by
sr.stockid
,sr.modelloid
,sr2.objid



------------- SECOND QUERY (WORK) ----------------
update
tblstock
set
npezzi = sr.npezzi
,npezzirimanenti = sr.npezzi
,modelloid = sr.modelloid
,objid = sr.objid
,tipostockid = 3
from
(select
sr.stockid
,sum(sr.npezzi) as npezzi
,sr.modelloid
,sr2.objid
from
tblstockrighe sr join tblstockrighe sr2
on sr.modelloid = sr2.modelloid and sr2.id = 110
where
sr.stockid = 270
group by
sr.stockid
,sr.modelloid
,sr2.objid
) sr
where
sr.stockid = tblstock.id

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Massimo Fidanza" <malix0 (AT) gmail (DOT) com> writes:
It still works, if you enable add_missing_from. But I agree that the
example shouldn't assume that.

That appears to be an entirely unrelated issue, which is whether
aggregates in the SET list of an UPDATE make any sense. I'm inclined
to think not --- what are you aggregating over?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I wrote:
I take that back --- FROM is not the issue (indeed, it has one).
The problem is the ORDER BY and LIMIT clauses. I dunno who put
in this example, but I'd like some of what they were smoking.
I've taken it out again, since without that it's not really showing
anything that the prior examples don't cover.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_8411_7063048.1141950394229
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Excuse me if I was no very clear and I don't put you in right direction,
But yes the problem is not with FROM but with ORDER BY or GROUP BY.
As you can see in my query I need to GROUP BY modelloid ad aggregate
on npezzi. To solve the problem I rewrite the query in the second for and
encapsulated the subquery with GROUP BY in (). This for work without
problem.


Bye Massimo Fidanza

2006/3/9, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us>:
------=_Part_8411_7063048.1141950394229
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Excuse me if I was no very clear and I don't put you in right direction,<br=
zzi. To solve the problem I rewrite the query in the second for and=20
<br>encapsulated the subquery with GROUP BY in (). This for work without pr=
oblem.<br><br><br>Bye Massimo Fidanza<br><br><div><span class=3D"gmail_quot=
e">2006/3/9, Tom Lane &lt;<a href=3D"mailto:tgl (AT) sss (DOT) pgh.pa.us">tgl (AT) sss (DOT) pgh.=
pa.us
</a>&gt;:</span><blockquote class=3D"gmail_quote" style=3D"border-left: 1px=
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I=
wrote:<br>&gt; &quot;Massimo Fidanza&quot; &lt;<a href=3D"mailto:malix0@gm=
ail.com">
malix0 (AT) gmail (DOT) com</a>&gt; writes:<br>&gt;&gt; this query is not correct and =
doesn't work with postgresql 8.1.<br><br>&gt; It still works, if you enable=
add_missing_from.<br><br>I take that back --- FROM is not the issue (indee=
d, it has one).
<br>The problem is the ORDER BY and LIMIT clauses.&nbsp;&nbsp;I dunno who p=
ut<br>in this example, but I'd like some of what they were smoking.<br>I've=
taken it out again, since without that it's not really showing<br>anything=
that the prior examples don't cover.
<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbs=
p;regards, tom lane<br></blockquote></div><br>

------=_Part_8411_7063048.1141950394229--