update sequence value dynamically - plsql

Question

I have a table with three columns col_a,col_b and col_c. col_a and col_b has values and col_c has null values. I just want to update the col_c with start sequence of col_a corresponding col_b. Expecting values of col_c is shown below. Using cursor to achieve this scenario.

COL_A       COL_B       COL_C

     1          1         1   
     2          1         2  
     3          1         3 
     4          1         4  
     5          1         5  
     6          1         6  
     7          1         7  
     8          1         8  
     9          1         9  
    10          1        10  
   101          2       101
   102          2       102
   104          2       103
   106          2       104
   107          2       105
   108          2       106
   110          2       107
   201          3       201
   202          3       202
   203          3       203
   204          3       204
   205          3       205
   301          5       301
   302          5       302
   305          5       303
   306          5       304

Tim Biegeleisen · Accepted Answer · 2020-03-11 02:04:23Z

1

One approach is to use a correlated subquery which finds, for each COL_B set of records, the minimum COL_A value to start the sequence. To this value we add the appropriate offset using COUNT.

UPDATE yourTable t1
SET COL_C = (SELECT MIN(t2.COL_A) +
                    COUNT(CASE WHEN t2.COL_A < t1.COL_A THEN 1 END)
             FROM yourTable t2
             WHERE t2.COL_B = t1.COL_B);

edited Mar 11, 2020 at 2:04

answered Mar 11, 2020 at 1:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

5 Comments

Prog_2918 Over a year ago

Thanks for your reponse sir. It reversely updating....

SQL> UPDATE tmp123 t1    SET COL_C = (SELECT MIN(t2.COL_A) +                         COUNT(CASE WHEN t2.COL_A > t1.COL_A THEN 1 END)                  FROM tmp123 t2                  WHERE t2.COL_B = t1.COL_B);

Tim Biegeleisen Over a year ago

Please reload your page as I have updated my answer.

Prog_2918 Over a year ago

This update query working great. Shall i use it in plsql block using cursor?

Tim Biegeleisen Over a year ago

I am not so familiar with cursors in Oracle. The update should work standalone just as well.

Prog_2918 Over a year ago

Thanks for the help sir @Tim Biegeleisen learned a useful scenario.

Popeye · Accepted Answer · 2020-03-11 01:53:01Z

0

You can take advantage of the analytical function as following:

Merge into your_table t
Using
(Select col_a, col_b,
       First_value(col_a) over (partition by col_b order by col_a)
       + row_number() over (partition by col_b order by col_a) - 1 as col_c
   From your_table) S
On (t.col_a = s.col_a and t.col_b = s.col_b)
When matched then 
Update set t.col_c = s.col_c

Cheers!!

answered Mar 11, 2020 at 1:53

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

update sequence value dynamically - plsql

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related