Help... update with sum()

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 Tue Aug 06 2002 - 19:43:40 CEST