From: | Jeff Kowalczyk <jtk(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | update table where rows are selected by inner join? |
Date: | 2004-05-10 22:13:44 |
Message-ID: | pan.2004.05.10.22.13.43.377272@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Witney | 2004-05-11 00:09:34 | Subqueries returning more than one value? |
Previous Message | Stephan Szabo | 2004-05-10 22:09:43 | Re: update table where rows are selected by inner join? |