Update a column in Oracle SQL Developer with sum of values of same other rows

Question

I have a table t1:

Id  Period  Cap_Up  Cap_Down
=============================
1000   1     100     200
1000   2     500     600
1001   1     200     400
1001   2     300     150
1002   1     900     500
1002   2     250     600

I want columns Cap_Up and Cap_Down be updated for Id=1000 based on values of these column for Id=1001 and Id=1002, for all periods, as follows:

Cap_Up(1000)   = Cap_Up(1001) + Cap_Down(1002) 
Cap_Down(1000) = Cap_Down(1001) + Cap_Up(1002)

So, the output will be, t1:

Id  Period  Cap_Up  Cap_Down
=============================
1000   1     700     1300
1000   2     900     400
1001   1     200     400
1001   2     300     150
1002   1     900     500
1002   2     250     600

Przemyslaw Kruglej · Accepted Answer · 2013-10-08 13:58:52Z

3

Below is one possible solution:

CREATE TABLE test_table (
  id NUMBER,
  period NUMBER,
  cap_up NUMBER,
  cap_down NUMBER
);

INSERT INTO test_table VALUES (1000, 1, 100, 200);
INSERT INTO test_table VALUES (1000, 2, 500, 600);
INSERT INTO test_table VALUES (1001, 1, 200, 400);
INSERT INTO test_table VALUES (1001, 2, 300, 150);
INSERT INTO test_table VALUES (1002, 1, 900, 500);
INSERT INTO test_table VALUES (1002, 2, 250, 600);

UPDATE test_table t1
  SET (cap_up, cap_down) =
    (SELECT t_1001.cap_up + t_1002.cap_down,
            t_1001.cap_down + t_1002.cap_up
      FROM test_table t_1001, test_table t_1002
     WHERE t_1001.id = 1001
      AND t_1002.id = 1002
      AND t_1001.period = t1.period
      AND t_1002.period = t1.period)
WHERE t1.id = 1000
;

Check at SQLFiddle: http://sqlfiddle.com/#!4/e9c61/1

answered Oct 8, 2013 at 13:58

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hamidreza · Accepted Answer · 2013-10-09 10:50:22Z

0

try this query it can help you so much because you can control this query with where clause:

create table t2 as
(select t1.id,t1.period,
(select t2.cap_up from hatest1 t2 where t2.period = t1.period and t2.id = t1.id+1)+
(select t2.cap_down from hatest1 t2 where t2.period = t1.period and t2.id = t1.id+2) as cap_up,
(select t2.cap_down from hatest1 t2 where t2.period = t1.period and t2.id = t1.id+1)+
(select t2.cap_up from hatest1 t2 where t2.period = t1.period and t2.id = t1.id+2) as cap_down
from t1 where t1.id = 1000);


insert into hatest2(select * from hatest1 where id>1000);

answered Oct 9, 2013 at 10:50

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Collectives™ on Stack Overflow

Update a column in Oracle SQL Developer with sum of values of same other rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related