X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BE39BD1B93D for ; Thu, 23 Oct 2003 19:44:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 22813-01 for ; Thu, 23 Oct 2003 16:44:15 -0300 (ADT) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id D7DF9D1CA59 for ; Thu, 23 Oct 2003 16:33:26 -0300 (ADT) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AClD4-0006qq-00 for ; Thu, 23 Oct 2003 21:33:26 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org Received: from sea.gmane.org ([80.91.224.252]) by main.gmane.org with esmtp (Exim 3.35 #1 (Debian)) id 1AClD3-0006qi-00 for ; Thu, 23 Oct 2003 21:33:25 +0200 Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AClD3-000427-00 for ; Thu, 23 Oct 2003 21:33:25 +0200 From: Jeff Kowalczyk Subject: help on update subselect with joins Date: Thu, 23 Oct 2003 15:33:24 -0400 Lines: 65 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.) X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_20, FORGED_YAHOO_RCVD, USER_AGENT X-Spam-Level: X-Archive-Number: 200310/316 X-Sequence-Number: 15716 I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid, then an expanded version that will work on all orderids with a specific customerinvoiceid as a parameter. I'm sure appropriate joins will handle it, but I'm not making any headway, everything comes back with multiple tuple selected for update errors. Any help would be greatly appreciated. Thanks. UPDATE ordercharges INNER JOIN orders ON orders.orderid = ordercharges.orderid SET orderchargeasbilled = (SELECT .065*orderchargeasbilled FROM ordercharges WHERE ordercharges.orderid='123456' AND orderchargecode = 'SALE') WHERE ordercharges.orderchargecode='S&H' AND ordercharges.orderid = '123456' (additional join and where for customerinvoiceid omitted/not attempted yet) orders: +-orderid | customerinvoiceid | (...) | | ordercharges: | orderchargeid +---orderid orderchargeasbilled (...) To Illustrate, I [ordercharges]------------------------------------------------- orderchargeid | orderid | orderchargecode | orderchargeasbilled --------------------------------------------------------------- 1 123456 SALE 10.00 2 123456 S&H (update) 3 123457 SALE 15.00 4 123457 EXPEDITE 5.00 5 123457 S&H (update) 6 123458 SALE 20.00 7 123458 S&H (update) 8 123459 SALE 10.00 9 123459 S&H (update) --------------------------------------------------------------- [orders]------------------- orderid | customerinvoiceid --------------------------- 123456 54321 123457 54321 123458 54321 123459 55543 --------------------------- (e.g. use 54321 as parameter to update 3 S&H rows in 3 orders, but not 1 S&H row in order 123459)