CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
pn_
pn_

Reputation: 107

Update column with values from another column using a cursor

I have a table table with 2 columns column1 and column2. I'm doing the following query in order to fetch all the duplicated rows and enumerate the rows in each group of duplicated rows:

CURSOR c
    IS
      SELECT groupnum,
             column1,
             column2,
             seqnum
        FROM (SELECT t.*,
                     COUNT (*) OVER (PARTITION BY column1, column2)
                         AS cnt,
                     DENSE_RANK () OVER (ORDER BY column1, column2)
                         AS groupnum,
                     ROW_NUMBER ()
                     OVER (PARTITION BY column1, column2
                           ORDER BY column1)
                         AS seqnum
                FROM table) t
       WHERE cnt > 1
    ORDER BY groupnum;

For a table table with these values

+------------+---------+
| COLUMN1    | COLUMN2 |
+------------+---------+
| a          | x       |
| a          | x       |
| o          | o       |
| o          | o       |
+------------+---------+

the cursor would retrieve this data:

+----------+------------+------------+--------+
| GROUPNUM | COLUMN1    | COLUMN2    | SEQNUM |
+----------+------------+------------+--------+
| 1        | a          | x          | 1      |
| 1        | a          | x          | 2      |
| 2        | o          | o          | 1      |
| 2        | o          | o          | 2      |
+----------+------------+------------+--------+

My goal is to pick every value from the column SEQNUM and to update the correspondent fields in the column column1 in the table table:

+------------+---------+
| COLUMN1    | COLUMN2 |
+------------+---------+
| a1         | x       |
| a2         | x       |
| o1         | o       |
| o2         | o       |
+------------+---------+

I tried a cursor FOR loop but I can't figure it out how to establish the correspondence between each field in column1 and each SEQNUM.

Upvotes: 0

Views: 612

Answers (1)

vav
vav

Reputation: 4684

you need to find a way to identify each record. The easiest way is to use rowid (we are in oracle, right?):

 select rowid, 
 DENSE_RANK () OVER (ORDER BY column1, column2) groupnum
 column1, column2, ROW_NUMBER () OVER 
 (PARTITION BY column1, column2 ORDER BY rowid) seqnum
 from table1
 where (column1, column2) in 
 (select column1, column2 
 from table1 
 group by column1, column2 
 having count(*) > 1)

I modified your query (my groupnum is different from yours as I am not counting unique groups), my seqnum is stable and will not change between runs.

So, to make an update, I would join above query to the table1 by rowid.

Upvotes: 1

Related Questions