oracle update with while loop

Question

I need to update table b with data from table A please advise, please the case

table A as below

|ID    |       balance|
-----------------------
|10    |       360    |
-----------------------
|20    |       300    |
-----------------------

table B as below

----------------------------------------------------------
|ID   |  count   |    value   |  settlementvalue |  mode |
----------------------------------------------------------

10     1           100        100 
10     2           100        100
10     3           100        null
10     4           100        null
10     5           100        null
10     6           100        null
10     7           100        null
10     8           100        null
10     9           100        null
10     10          100        null

20     1           100        100
20     2           100        null
20     3           100        null
20     4           100        null
20     5           100        null
20     6           100        null
20     7           100        null
20     8           100        null
20     9           100        null
20    10           100        null

i need to read the balance from table A and update the first null value in settlement value in table B with value in column value and then decrease the balance then we go to update the next row until the balance become less than the value we will put it in mod column

the final result should be as below: table B as below

ID     count       value     settlementvalue   mode
10     1           100        100
10     2           100        100
10     3           100        100
10     4           100        100
10     5           100        100              
10     6           100        null              60
10     7           100        null
10     8           100        null
10     9           100        null
10     10          100        null

20     1           100        100
20     2           100        100
20     3           100        100
20     4           100        100
20     5           100        null
20     6           100        null
20     7           100        null
20     8           100        null

zip · Accepted Answer · 2020-02-15 14:06:04Z

1

You can do it this way, with cte and windows functions. You won't need while loops for this.

    with _modeTbl as
(
select b.*, a.balance ,
balance - sum(value) OVER (PARTITION BY a.ID ORDER BY count ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as _mode
from tableA a inner join tableB b on a.ID = b.ID where settlementvalue is null
),
finalcte as
(
select ID, count, value 
,case when _mode > = 0 then value else null end as settlementvalue
,case when _mode < value and _mode > 0 then _mode else null end as mode
from _modeTbl
union
select *
from tableB where settlementvalue is not null
)
select ID, count, value, settlementvalue,
lag(mode) OVER (PARTITION BY ID ORDER BY ID) as mode
from finalcte

Output is:

ID  count   value   settlementvalue mode
10     1    100      100            NULL
10     2    100      100            NULL
10     3    100      100            NULL
10     4    100      100            NULL
10     5    100      100            NULL
10     6    100      NULL           60
10     7    100      NULL           NULL
10     8    100      NULL           NULL
10     9    100      NULL           NULL
10     10   100      NULL           NULL
20     1    100      100            NULL
20     2    100      100            NULL
20     3    100      100            NULL
20     4    100      100            NULL
20     5    100      NULL           NULL
20     6    100      NULL           NULL
20     7    100      NULL           NULL
20     8    100      NULL           NULL
20     9    100      NULL           NULL
20     10   100      NULL           NULL

edited Feb 15, 2020 at 14:06

answered Feb 14, 2020 at 23:39

zip

4,0612 gold badges13 silver badges22 bronze badges

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

3 Comments

user1724852 Over a year ago

many thanks for your contribution, but i need the mode to be in next count not in the same row as output should be |ID-----count------value-------settlementvalue---------mode| |10-----5-----------100---------------100------------------NULL| |10-----6-----------100----------------NULL-----------------60--|

zip Over a year ago

I fixed it. Let me know

user1724852 Over a year ago

i fix the first solution as i change the last case when clause as belo case when mode < 0 and ABS(_mode) < VALUE and ABS(_mode) > 0 then _mode+value else null end as mode many thanks for your valued contribution

Collectives™ on Stack Overflow

oracle update with while loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related