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 DC44CD1B1E6 for ; Mon, 10 May 2004 19:10:14 -0300 (ADT) 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 18922-07 for ; Mon, 10 May 2004 19:09:43 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 178F1D1B21F for ; Mon, 10 May 2004 19:09:41 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 71A8135520; Mon, 10 May 2004 15:09:43 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 701E935512; Mon, 10 May 2004 15:09:43 -0700 (PDT) Date: Mon, 10 May 2004 15:09:43 -0700 (PDT) From: Stephan Szabo To: Jeff Kowalczyk Cc: pgsql-sql@postgresql.org Subject: Re: update table where rows are selected by inner join? In-Reply-To: Message-ID: <20040510150800.Y21776@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/91 X-Sequence-Number: 17805 On Mon, 10 May 2004, Jeff Kowalczyk wrote: > I have two tables orders and customerpaymentnote, which keep denormalized > columns of the status in rows related by orderid. The column duplication > is intentional, to ease end-user ad-hoc queries. I don't understand the > UPDATE FROM clause at: > http://www.postgresql.org/docs/7.4/static/sql-update.html > > I have a query working (slowly) in MS Access with the pgodbc driver, but I > need to know the Postgres version of the SQL. > > Working (but slow) MS Access version: > UPDATE orders > INNER JOIN customerpaymentnote > ON orders.orderid=customerpaymentnote.orderid > SET orders.customerdatetimepaid = customerpaymentnote.customerdatetimepaid, > orders.customerchargeaspaid = customerpaymentnote.customerchargeaspaid, > orders.customerchargestatus = "Payment-Recieved", > orders.orderworkflowbillingstateid = "Payment-Recieved"; > > Postgres translation, which does not yet work: > UPDATE orders > SET customerdatetimepaid = customerpaymentnote.customerdatetimepaid, > customerchargeaspaid = customerpaymentnote.customerchargeaspaid, > customerchargestatus = "Payment-Recieved", > orderworkflowbillingstateid = "Payment-Recieved" > FROM orders INNER JOIN customerpaymentnote > ON orders.orderid=customerpaymentnote.orderid I think you just want something like: FROM customerpaymentnote WHERE orders.orderid=customerpaymentnot.orderid; The update table (orders in this case) is effectively already included in the "from" list.