pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: update table where rows are selected by inner join?
2+ messages / 2 participants
[nested] [flat]
* update table where rows are selected by inner join?
@ 2004-05-10 22:13 Jeff Kowalczyk <jtk@yahoo.com>
2004-05-10 22:09 ` Re: update table where rows are selected by inner join? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jeff Kowalczyk @ 2004-05-10 22:13 UTC (permalink / raw)
To: pgsql-sql
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
Query result with 0 rows will be returned.
ERROR: table name "orders" specified more than once
Can anyone suggest a proper translation. I'm open to other query
strategies, if an IN(...) statement or something else will speed up this
slow query.
Thanks.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: update table where rows are selected by inner join?
2004-05-10 22:13 update table where rows are selected by inner join? Jeff Kowalczyk <jtk@yahoo.com>
@ 2004-05-10 22:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-05-10 22:09 UTC (permalink / raw)
To: Jeff Kowalczyk <jtk@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-05-10 22:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-10 22:13 update table where rows are selected by inner join? Jeff Kowalczyk <jtk@yahoo.com>
2004-05-10 22:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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