CODEWITHSUNDEEP
sqloracle-database
Sid
Sid

Reputation: 582

Updating column based on previous record

I have a table with Columns C1, C2, C3, C4, C5, C6 and Count. I have to order the records using order by C1, C2, C3, C4, C5. For 1st record Count will be 0 then if Values of C1, C2, C3, C4, C5 of the next row is same as previous row then I have to update the Count column by adding +1. So this is what I am expecting: Table:

C1   C2    C3    C4    C5    C6     Count
-------------------------------------------
1    A     X     X1    AA    123     0
1    A     X     X1    AA    121     1
1    A     X     X1    AA    118     2
1    A     X     X1    AA    117     3
2    B     X     X2    AA    234     0
8    A     Y     X3    AA    298     0
8    A     Y     X3    AA    800     1

I am using Oracle DB. I wrote a PL SQL block with Cursor with Order by and then used the cursor in BULK COLLECT and iterated through each record compared with previous record. However, Count column is updated with unexpected record and taking much more time than expected, table have around 400k records. I unable to type my code here as actual code is in client machine. Any help is highly appreciated. One can use PL/SQL or Simple SQL. DB is Oracle.

Upvotes: 0

Views: 840

Answers (2)

Sid
Sid

Reputation: 582

With the help of hint provided by Gordon Linoff I am able to solve this issue. However., I created a new table instead of update to avoid performance issue. Below is the query:

CREATE TABLE TAB2 AS
SELECT T1.C1, T1.C2, T1.C3, T1.C4, T1.C5, T1.C6, T1.REC_COUNT 
(select t.*,
       (row_number() over (partition by c1, c2, c3, c4, c5 order by c1, c2, c3, c4, c5) - 1
       ) as rec_count
from tab1 T) T1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I believe you want row_number():

select t.*,
       (row_number() over (partition by c1, c2, c3, c4, c5 order by c6) - 1
       ) as "Count"
from t;

You can use merge to put this in an update statement. Alternatively, if you really want an update:

update t
    set count = (select count(*)
                 from t t2
                 where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.col3 = t.col3 and
                       t2.col4 = t.col4 and t.col5 = t2.col5 and
                       t2.col6 < t.col6
                );

Upvotes: 2

Related Questions