Thread: update help

I'd like to know what's the correct SQL statement to do the following:

update    t1 a
set    a.amount = sum(b.amount)
from    t2 b
where    a.id = b.id

update t1
set amount = sum(b.amount)
from ts b
where a.id=b.id

On Thursday 18 January 2001 09:54, Carolyn Wong wrote:
> I'd like to know what's the correct SQL statement to do the following:
>
> update    t1 a
> set    a.amount = sum(b.amount)
> from    t2 b
> where    a.id = b.id

Carolyn Wong <carolyn@greatpacific.com.au> writes:
> I'd like to know what's the correct SQL statement to do the following:
> update    t1 a
> set    a.amount = sum(b.amount)
> from    t2 b
> where    a.id = b.id

Try

UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id);

Or possibly you want

UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id)
WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id);

depending on whether you mean to zero out any t1 rows that have no
matching rows in t2.  Note that you cannot attach an alias name to the
target table, you have to use its real name in the subselects.

There was a thread about this very issue a few months ago, and IIRC
we decided that an aggregate in an UPDATE doesn't have well-defined
semantics.  The SQL92 spec explicitly disallows it.  Right now PG will
take it, but we probably do something pretty surprising :-(
        regards, tom lane

This update field with the sum of all amounts in t2. I want to update
sum of each individual IDs.


Tubagus Nizomi wrote:
> 
> update t1
> set amount = sum(b.amount)
> from ts b
> where a.id=b.id
> 
> On Thursday 18 January 2001 09:54, Carolyn Wong wrote:
> > I'd like to know what's the correct SQL statement to do the following:
> >
> > update        t1 a
> > set   a.amount = sum(b.amount)
> > from  t2 b
> > where a.id = b.id

Forgot to mention that I'm using V6.5. It doesn't seem to like
subqueries, got the following error:

ERROR:  parser: parse error at or near "select"

What I really want to do is follows

t2:    ID    Amount ........-----------------------1    1    ......1    2    ......2    3    ......2    2    ......

and want the following result in t1

t1:    ID    Amount .......-----------------------1    3    ......2    5    ......

Tom Lane wrote:
> 
> Carolyn Wong <carolyn@greatpacific.com.au> writes:
> > I'd like to know what's the correct SQL statement to do the following:
> > update        t1 a
> > set   a.amount = sum(b.amount)
> > from  t2 b
> > where a.id = b.id
> 
> Try
> 
> UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id);
> 
> Or possibly you want
> 
> UPDATE t1 SET amount = (select sum(b.amount) from t2 b WHERE t1.id = b.id)
> WHERE EXISTS (select * FROM t2 b WHERE t1.id = b.id);
> 
> depending on whether you mean to zero out any t1 rows that have no
> matching rows in t2.  Note that you cannot attach an alias name to the
> target table, you have to use its real name in the subselects.
> 
> There was a thread about this very issue a few months ago, and IIRC
> we decided that an aggregate in an UPDATE doesn't have well-defined
> semantics.  The SQL92 spec explicitly disallows it.  Right now PG will
> take it, but we probably do something pretty surprising :-(
> 
>                         regards, tom lane

Tom,

> UPDATE t1 SET amount = (select sum(b.amount) from t2 b
> WHERE t1.id = b.id);

Interesting.  I'm used to (not necessarily in PGSQL):

UPDATE t1 SET amount = t2ttl.totalamount
FROM (SELECT sum(amount) as totalamount,    id FROM t2 GROUP BY id) t2ttl
WHERE t1.id = t2.id

Although this is a subselect in the FROM clause, something
we've had trouble with as I recall.

-Josh Berkus

Carolyn Lu Wong <carolyn@greatpacific.com.au> writes:
> Forgot to mention that I'm using V6.5.

Oh.  6.5's support for sub-selects is pretty limited :-(.
I think the only way to do it in 6.5 is with a temp table,
eg

SELECT id, sum(amount) as sum into temp table tt from t2 group by id;

update t1 set amount = tt.sum where id = tt.id;

drop table tt;

You might wanna think about updating sometime soon...
        regards, tom lane