Thread: Putting an aggregate value in an UPDATE statement...

This statement:


UPDATE question_choices SET total_rows = count(*) from care_lesson;


returns the following error (which is well-documented):


ERROR:  cannot use aggregate function in UPDATE
LINE 1: UPDATE question_choices SET total_rows = count(*) from care_...


I wondered how I can put a row count from one table into another?  I
can't figure it out.

Thanks very much,

John Gage

See Below:




--

- Bret
____________________________________________
"Why should I fret in microcosmic bonds
That chafe the spirit, and the mind repress,
When through the clouds gleam beckoning beyonds
Where shining vistas mock man's littleness?"
- H.P. Lovecraft, "Phaeton" (1918)

Thanks.  I tried that _without_ the parentheses around the select statement and it didn't work.  

On Tuesday 1. June 2010 23.03.51 John Gage wrote:
> Thanks.  I tried that _without_ the parentheses around the select
> statement and it didn't work.
>
> At the risk of further displaying ignorance, why are the parens
> necessary, what do they do?

I don't know the technical answer, but as an intuitively derived rule of
thumb: Whenever you need the output of a subselect in an outer query, put the
subselect in parentheses. It becomes second nature after a while.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

John Gage <jsmgage@numericable.fr> writes:
> Thanks.  I tried that _without_ the parentheses around the select
> statement and it didn't work.

> At the risk of further displaying ignorance, why are the parens
> necessary, what do they do?

You need them to syntactically separate the sub-select from the outer
select.  If SQL didn't require them, then in something like

    UPDATE question_choices SET total_rows =
        select count(*) from care_lesson where something

it wouldn't be clear whether the WHERE clause was meant to attach
to the sub-select or the outer UPDATE.

            regards, tom lane

> You need them to syntactically separate the sub-select from the outer
> select.  If SQL didn't require them, then in something like
>
>     UPDATE question_choices SET total_rows =
>         select count(*) from care_lesson where something
>
> it wouldn't be clear whether the WHERE clause was meant to attach
> to the sub-select or the outer UPDATE.

A couple of days ago, a was a little stumped by this. I had written a plain
SQL function with one integer parameter, and then tried to use a SELECT as
input parameter as in

SELECT myfunc(SELECT foo FROM bar WHERE baz);

It took a while before I realized that I needed to put the query in another
set of parentheses:

SELECT myfunc((SELECT foo FROM bar WHERE baz));

worked just fine. I fail to see the ambiguity here, though.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

Leif Biberg Kristensen <leif@solumslekt.org> writes:
>> You need them to syntactically separate the sub-select from the outer
>> select.  If SQL didn't require them, then in something like
>>
>> UPDATE question_choices SET total_rows =
>> select count(*) from care_lesson where something
>>
>> it wouldn't be clear whether the WHERE clause was meant to attach
>> to the sub-select or the outer UPDATE.

> A couple of days ago, a was a little stumped by this. I had written a plain
> SQL function with one integer parameter, and then tried to use a SELECT as
> input parameter as in

> SELECT myfunc(SELECT foo FROM bar WHERE baz);

> It took a while before I realized that I needed to put the query in another
> set of parentheses:

> SELECT myfunc((SELECT foo FROM bar WHERE baz));

> worked just fine. I fail to see the ambiguity here, though.

Well, the main point is that the possible ambiguity means that SQL has
mandated you put parentheses around any sub-select.  However, if you're
claiming there is no possible ambiguity inside a function call, compare:

    select myfunc((select integer_col from foo where bar = 5+4))
    select myfunc((select integer_col from foo where bar = 5)+4)

These mean different things, and you couldn't tell 'em apart without
the inner parentheses.

            regards, tom lane

On Tuesday 1. June 2010 23.34.39 Tom Lane wrote:
> Well, the main point is that the possible ambiguity means that SQL has
> mandated you put parentheses around any sub-select.  However, if you're
> claiming there is no possible ambiguity inside a function call, compare:
>
>     select myfunc((select integer_col from foo where bar = 5+4))
>     select myfunc((select integer_col from foo where bar = 5)+4)
>
> These mean different things, and you couldn't tell 'em apart without
> the inner parentheses.

Right, I get it. Thanks for the explanation.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

In the first place, thanks a very great deal for all the feedback.
Much appreciated.

In the second, the UPDATE statement I used as an example was really a
kludge because I could not figure out how to place the same SELECT
COUNT(*) expression into an INSERT statement.  Turns out that by
enclosing _it_ in parentheses, everything is cool.  Superb.

John


> On Tuesday 1. June 2010 23.34.39 Tom Lane wrote:
>> Well, the main point is that the possible ambiguity means that SQL
>> has
>> mandated you put parentheses around any sub-select.

Okay I have a similar but seemingly more complicated Problem with aggregate
values in an UPDATE statement:

update test set ew_data_vm_fraction =
array_agg(unnest(ew_data_vm) * area_percentage)

gives me the obvious "ERROR: cannot use aggregate function in UPDATE"
message

when I set i in parentheses

update test set ew_data_vm_fraction =
(select array_agg(unnest(ew_data_vm) * area_percentage)
from test

it results in the message: "ERROR: set-valued function called in context
that cannot accept a set"

i am really desperate,what can I try else?

Greeting

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Putting-an-aggregate-value-in-an-UPDATE-statement-tp2140836p5531159.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Okay I have a similar but seemingly more complicated Problem with aggregate
values in an UPDATE statement:

update test set ew_data_vm_fraction =
array_agg(unnest(ew_data_vm) * area_percentage)

gives me the obvious "ERROR: cannot use aggregate function in UPDATE"
message

when I set i in parentheses

update test set ew_data_vm_fraction =
(select array_agg(unnest(ew_data_vm) * area_percentage)
from test

it results in the message: "ERROR: set-valued function called in context
that cannot accept a set"

i am really desperate,what can I try else?

Greetings!

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Putting-an-aggregate-value-in-an-UPDATE-statement-tp2140836p5531137.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On 02/03/2012 14:38, rajanski wrote:
> Okay I have a similar but seemingly more complicated Problem with aggregate
> values in an UPDATE statement:
>
> update test set ew_data_vm_fraction =
> array_agg(unnest(ew_data_vm) * area_percentage)
>
> gives me the obvious "ERROR: cannot use aggregate function in UPDATE"
> message
>
> when I set i in parentheses
>
> update test set ew_data_vm_fraction =
> (select array_agg(unnest(ew_data_vm) * area_percentage)
> from test
>
> it results in the message: "ERROR: set-valued function called in context
> that cannot accept a set"
>
> i am really desperate,what can I try else?
>
> Greetings!
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Putting-an-aggregate-value-in-an-UPDATE-statement-tp2140836p5531137.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>

Hi Rajanski

Your basic problem is you're not handling the set correctly.  For one
thing you're multiplying a set by a number.   Consider what happens if
you unnest one row (select the row as id=1):

SELECT unnest(ew_data_vm) from test WHERE id = 1;

This produces many rows and one column "unnest".  But this is really
misleading.  You select one row and get many.  It would make much more
sense to write something like:

SELECT unnest FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );

...

SELECT unnest * area_percentage
FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );

...

SELECT array_agg(unnest * area_percentage)
FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );



Then to update you get:

update test set ew_data_vm_fraction =
(select array_agg(unnest * area_percentage) FROM unnest(ew_data_vm) )


Hope this helps