Update column with values from another column using a cursor

Question

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.

vav · Accepted Answer · 2014-05-28 01:24:41Z

1

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.

edited May 28, 2014 at 1:24

answered May 27, 2014 at 20:32

vav

4,6942 gold badges22 silver badges40 bronze badges

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

2 Comments

pn_ Over a year ago

you're missing a coma right after groupnum, right? is seqnum now stable because you're ordering by rowid?

vav Over a year ago

Yes to both questions :)

Collectives™ on Stack Overflow

Update column with values from another column using a cursor

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related