multi row update using sub query

[fitzini]

Hi all,

I need to update multiple rows using a sub query. I've pasted sample code below to hopefully help recreate what I'm trying to do.

drop table dummy_test

create table dummy_test
(cmr number
,sub number
,rev number
,cost number)
/
insert into dummy_Test values (1,2,0, 0)
/
insert into dummy_Test values (1,3,0, 0)
/
select *
from dummy_test
/
create table dollar_amounts
(cmr number
,sub number
,rev number
,rev_direction varchar2(1))
/
insert into dollar_amounts values (1, 2, 48, 'I')
/
insert into dollar_amounts values (1, 2, .63, 'I')
/
insert into dollar_amounts values (1, 2, 17, 'O')
/
insert into dollar_amounts values (1, 2, .17, 'O')
/
insert into dollar_amounts values (1, 3, 19, 'I')
/
insert into dollar_amounts values (1, 3, .83, 'I')
/
insert into dollar_amounts values (1, 3, 10, 'O')
/
insert into dollar_amounts values (1, 3, .93, 'O')
/
select *
from dollar_amounts
/

I want to update dummy_test with the sum of the revenue and costs from the dollar_amounts table.

When I do this I can but the row return more than one 1 in this statement.

update dummy_test a
set (rev, cost) =
(select (case when rev_direction = 'I' then sum(rev) end)
,(case when rev_direction = 'O' then sum(rev) end)
from dollar_amounts b
where b.cmr = a.cmr
and b.sub = a.sub
group by reV_direction)

-------------

and when I try this it doesn't recognise the table I want to update, in this instance table a.

update dummy_test a
set (rev, cost) = (
select sum(rev), sum(cost)
from (
(select (case when rev_direction = 'I' then sum(rev) end) rev
,(case when rev_direction = 'O' then sum(rev) end) cost
from dollar_amounts b
where b.cmr = a.cmr
and b.sub = a.sub
group by rev_direction)) )

this is what I'm trying to achieve when updating the table:

select cmr, sub, sum(rev), sum(cost)
from (
select cmr, sub
,(case when rev_direction = 'I' then sum(rev) end) rev
,(case when rev_direction = 'O' then sum(rev) end) cost
from dollar_amounts
group by rev_direction, cmr, sub)
group by cmr, sub

trying to find out the best approach for this. I'll be updating roughly 2 million rows in this update.

thanks in advance.