Hi, I need to update a table with a sub query. Here are the steps I'm following but I'm struggling to successfully update the table. the performance of this update has to be good because of the number of rows I'll be updating in production. any ideas? thanks in advance. 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

Software/Hardware used: