X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7CEE4533B7 for ; Tue, 19 Apr 2005 11:43:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05345-08 for ; Tue, 19 Apr 2005 14:43:40 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id 8AEF053486 for ; Tue, 19 Apr 2005 11:43:38 -0300 (ADT) Received: (qmail-ldap/ctrl 28092 invoked from network); 19 Apr 2005 14:43:40 -0000 Received: from unknown (HELO jupiter.sonorys.at) ([213.235.243.42]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with SMTP for ; 19 Apr 2005 14:43:40 -0000 Received: from neptun.sonorys.at (neptun.sonorys.at [172.24.1.17]) by jupiter.sonorys.at (8.11.7+Sun/8.11.7) with ESMTP id j3JEhg119596 for ; Tue, 19 Apr 2005 16:43:42 +0200 (MEST) Received: by neptun.sonorys.at with Internet Mail Service (5.5.2653.19) id <2JD5W279>; Tue, 19 Apr 2005 16:43:42 +0200 Message-ID: From: =?ISO-8859-1?Q?K=D6PFERL_Robert?= To: pgsql-sql@postgresql.org Subject: Re: trying to do an update a bit confused. Date: Tue, 19 Apr 2005 16:43:41 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C544EE.2DDA3EB0" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200504/292 X-Sequence-Number: 21185 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C544EE.2DDA3EB0 Content-Type: text/plain; charset="ISO-8859-1" You're most probably missing a Where clause after the parentensis. see: -----Original Message----- From: Joel Fradkin [mailto:jfradkin@wazagua.com] Sent: Dienstag, 19. April 2005 16:06 To: pgsql-sql@postgresql.org Subject: [SQL] trying to do an update a bit confused. update tblcase set merchandisetotal = ( COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount FROM tblmerchandise m WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text), 0.0) ) WHERE ; I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong? Joel Fradkin ------_=_NextPart_001_01C544EE.2DDA3EB0 Content-Type: text/html; charset="ISO-8859-1"
You're most probably missing a Where clause after the parentensis. see:
-----Original Message-----
From: Joel Fradkin [mailto:jfradkin@wazagua.com]
Sent: Dienstag, 19. April 2005 16:06
To: pgsql-sql@postgresql.org
Subject: [SQL] trying to do an update a bit confused.

update tblcase  set merchandisetotal =

(

COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount

           FROM tblmerchandise m

          WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text), 0.0)

) 

WHERE <condtion that selects one record>;

 

 

I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong?

 

Joel Fradkin

 

 

 

------_=_NextPart_001_01C544EE.2DDA3EB0--