From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Jeff Kowalczyk <jtk@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: update table where rows are selected by inner join?
Date: Mon, 10 May 2004 15:09:43 -0700 (PDT)
Message-ID: <20040510150800.Y21776@megazone.bigpanda.com> (raw)
In-Reply-To: <pan.2004.05.10.22.13.43.377272@yahoo.com>
References: <pan.2004.05.10.22.13.43.377272@yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, jtk@yahoo.com
Subject: Re: update table where rows are selected by inner join?
In-Reply-To: <20040510150800.Y21776@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox