Gerhard Heift, 07.11.2008 14:47:
What about:
UPDATE report_table SET order_count = s_count, order_value = s_value
FROM (SELECT count(*) AS s_count, sum(amount) AS s_value FROM order o
WHERE o.customer_id = report_table.customer_id)
Its untested, but I think, it must works like this.
Interesting idea (coming from Oracle I'm just not used to the FROM clause for UPDATE :) )
But unfortunately it gives an error:
ERROR: subquery in FROM cannot refer to other relations of same query
level [SQL State=42P10]
Ok, its a little bit more complicated:
UPDATE report_table SET order_count = s_count, order_value = s_value
FROM (SELECT customer_id, count(*) AS s_count, sum(amount) AS s_value
FROM order o GROUP BY customer_id) AS summary
WHERE summary.customer_id = report_table.customer_id)
Cool! Thanks a lot
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
