From: | David Gagnon <dgagnon(at)siunik(dot)com> |
---|---|
To: | PG-General <pgsql-general(at)postgresql(dot)org> |
Subject: | Problem with UPDATE .. FROM syntax. please help |
Date: | 2005-11-01 21:16:47 |
Message-ID: | 4367DB3F.9080502@siunik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I mess around with this request below. Has is I get the following error.
2005-11-01 16:08:41 ERROR: JOIN/ON clause refers to "pd", which is not
part of JOIN
The error came from the following line:
INNER JOIN BD ON PD.PDBDNUM = BD.BDNUM AND PD.PDYPNUM = BD.BDYPNUM
But when I remove the PD. I get
ERROR: column "pdypnum" does not exist.
If I try to add BD in the FROM clause I get a
ERROR: table name "pd" specified more than once
So is there a way I can make it work ?!
Thanks for your help :-)
/David
UPDATE PD SET PDSTOT = ROUND(PDCOUTNET * COALESCE(PDQRECUI,0), 2),
PDTAXF = ROUND(((PDCOUTNET * COALESCE(PDQRECUI,0))::NUMERIC
* ICTPS::INT * BO.BOTAUXF / 100), 2),
PDTAXP = ROUND((((PDCOUTNET * COALESCE(PDQRECUI,0))::NUMERIC
+ ATP.ATCALC::INT * ROUND(((PDCOUTNET * COALESCE(PDQRECUI,0))::NUMERIC *
ICTPS::INT * BO.BOTAUXF / 100), 2)) * COALESCE(IZ.IZTAXABLE,
ICTPROV)::INT * BO.BOTAUXP / 100), 2)
FROM PO
INNER JOIN BD ON PD.PDBDNUM = BD.BDNUM AND PD.PDYPNUM = BD.BDYPNUM
INNER JOIN BO ON BD.BDBONUM = BO.BONUM AND BD.BDYPNUM = BO.BOYPNUM
INNER JOIN IC ON BD.BDICNUM = IC.ICNUM
LEFT OUTER JOIN IZ ON PO.POAPNUMF = IZ.IZAPNUM AND IC.ICNUM = IZ.IZICNUM
WHERE PDPONUM = PO.PONUM AND PDYPNUM = PO.POYPNUM
AND PDPONUM = id AND PDYPNUM = companyId;
INNER JOIN BD ON PD.PDBDNUM = BD.BDNUM AND PD.PDYPNUM = BD.BDYPNUM
From | Date | Subject | |
---|---|---|---|
Next Message | Benjamin Smith | 2005-11-01 21:26:18 | Dumb Questions - upgrade notes? |
Previous Message | Benjamin Smith | 2005-11-01 21:06:18 | Re: Frequency of Analyze? |