By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,965 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,965 IT Pros & Developers. It's quick & easy.

multi row update using sub query

P: 1
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.
Jun 26 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.