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 90568D1B537 for ; Wed, 29 Oct 2003 14:53:52 +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 32656-10 for ; Wed, 29 Oct 2003 10:53:25 -0400 (AST) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id C6A7FD1B52F for ; Wed, 29 Oct 2003 10:53:20 -0400 (AST) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AErgr-0005MT-00 for ; Wed, 29 Oct 2003 15:52:53 +0100 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 1AErgq-0005ML-00 for ; Wed, 29 Oct 2003 15:52:52 +0100 Received: from news by sea.gmane.org with local (Exim 3.35 #1 (Debian)) id 1AErgq-00034A-00 for ; Wed, 29 Oct 2003 15:52:52 +0100 From: Jeff Kowalczyk Subject: Help on update that subselects other records in table, uses joins Date: Wed, 29 Oct 2003 09:52:52 -0500 Lines: 64 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/381 X-Sequence-Number: 15781 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, this is a sample table: [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)