Re: Help... update with sum()

The table design looks bad. If you really want to stick to this path, this simple SQL would update the total

update my_table t1 set total =
  (select sum(amount) from my_table t2 where t1.name=t2.name group by name);

sgb <sgb_at_sgb.com> wrote in message news:<tj20lu81qqmm5d2dii68atjliubt2forg8_at_4ax.com>...
> Any assistance with this problem would be really appreciated.
> Supposing I have a table like this: (Three columns: name, amount,
> total. I shall use commas to separate the fields)
>
> NAME, AMOUNT, TOTAL
> joe, 100,
> joe, 500,
> mary, 200,
> mary, 300,
> jane, 100,
>
>
> I would like to update the TOTAL (currently empty) with the sum of the
> amounts for names that are identical. IE:
> select sum(amount) from my_table where name = 'joe'; *** This would
> return 600.
>
> I have been trying to do this using pl/sql but I wonder if it could
> even be done with a simple sql statement? Even the pl/sql required has
> me stumped. Originally I was trying something like this (it obviously
> doesn't work):
>
> cursor total_cur is
> select sum(amount) from my_table where name = name for update;
> loop
> fetch total_cur into v_total;
> update my_table
> set total = v_total
> where current of;
>
>
> -Scott
Received on Wed Aug 07 2002 - 01:24:26 CEST