From: | "Massimo Fidanza" <malix0(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #2303: UPDATE from manual is incorrect |
Date: | 2006-03-06 15:09:58 |
Message-ID: | 20060306150958.30FB8F0B04@svr2.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2006-03-06 15:20:20 | Re: FATAL: semctl(1672698088, 12, SETVAL, 0) failed |
Previous Message | Alexander Pivovarov | 2006-03-06 11:03:24 | BUG #2302: \d command gets double quoted table name in error message - ""table_name"" |