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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
