The following bug has been logged online:
Bug reference: 2303
Logged by: Massimo Fidanza
Email address: [EMAIL PROTECTED]
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
