UPDATE tuples with a sub-select

On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote:

Hi,

are there any plans to support updating a tuple using a sub-select in one of the future versions.

e.g, something like:

UPDATE report_table
SET (order_count,order_value) = (SELECT count(*), sum(amount)
FROM order o
WHERE o.customer_id = report_table.customer_id);

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.

I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on the "roadmap"

Regards
Thomas

Regards,
Gerhard

On Fri, Nov 07, 2008 at 11:15:07AM +0100, Thomas Kellerer wrote:

Hi,

are there any plans to support updating a tuple using a sub-select in one of the future versions.

e.g, something like:

UPDATE report_table
SET (order_count,order_value) = (SELECT count(*), sum(amount)
FROM order o
WHERE o.customer_id = report_table.customer_id);

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.

I know it's currently not possible (so we need to revert to two sub-selects) but I was wondering if this somewhere on the "roadmap"

Regards
Thomas

Regards,
Gerhard

Gerhard Heift, 07.11.2008 13:35:

are there any plans to support updating a tuple using a sub-select in one of the future versions.

e.g, something like:

UPDATE report_table
SET (order_count,order_value) = (SELECT count(*), sum(amount)
FROM order o
WHERE o.customer_id = report_table.customer_id);

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]

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:

are there any plans to support updating a tuple using a sub-select in one of the future versions.

It's the first item under UPDATE on the TODO list ...

regards, tom lane

On Fri, Nov 07, 2008 at 02:31:42PM +0100, Thomas Kellerer wrote:

Gerhard Heift, 07.11.2008 13:35:

are there any plans to support updating a tuple using a sub-select in one of the future versions.

e.g, something like:

UPDATE report_table
SET (order_count,order_value) = (SELECT count(*), sum(amount)
FROM order o
WHERE o.customer_id = report_table.customer_id);

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)

Regards
Thomas

Regards,
Gerhard

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

Tom Lane, 07.11.2008 14:33:

Thomas Kellerer <spam_eater@gmx.net> writes:

are there any plans to support updating a tuple using a sub-select in one of the future versions.

It's the first item under UPDATE on the TODO list ...

That is good news :)

Thanks
Thomas