Updating a table with sub query

0 pts.
Tags:
SQL
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
ASKED: June 26, 2007  4:02 AM
UPDATED: June 28, 2007  12:40 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

I think you may use the following update statement :

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

Rumman

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following